Note: We no longer publish the latest version of our code here. We primarily use a kumc-bmi github organization. The heron ETL repository, in particular, is not public. Peers in the informatics community should see MultiSiteDev for details on requesting access.

source: heron_load/bsr_clinical_facts_load.sql @ 0:42ad7288920a

heron-michigan tip
Last change on this file since 0:42ad7288920a was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 6 years ago

Merge with demo_concepts_3800

File size: 2.3 KB
Line 
1/* bsr_demographics_load.sql -- load BSR demographic data into HERON repository
2
3Copyright (c) 2012 University of Kansas Medical Center
4part of the HERON* open source codebase; see NOTICE file for license details.
5* http://informatics.kumc.edu/work/wiki/HERON
6
7see also http://informatics.kumc.edu/work/wiki/HeronLoad ,
8         https://informatics.kumc.edu/work/wiki/BiospecimenRepository
9
10patterned after epic_facts_load.sql
11
12to start over, just:
13truncate table observation_fact_upload;
14
15This script inserts into observation_fact_upload all the bsr observations;
16see i2b2_facts_deid.sql for where the data goes from there.
17*/
18alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' ;
19
20select patient_num from NightHeronData.observation_fact where 1=0;
21select MRN from BSR.bsr_table where 1 = 0;
22
23truncate table observation_fact_upload;
24whenever sqlerror continue;
25alter table observation_fact_upload
26  disable constraint observation_fact_pk;
27whenever sqlerror exit;
28
29/***********************
30  BSR observation load
31 ***********************/
32insert into observation_fact_upload (
33      concept_cd, valtype_cd, tval_char,
34      patient_num, encounter_num, sub_encounter,
35      start_date,
36      end_date,
37      provider_id,
38      modifier_cd,
39      instance_num,
40      update_date,
41      import_date, upload_id, download_date, sourcesystem_cd)
42select
43      f.concept_cd, f.valtype_cd, f.tval_char,
44      pmap.patient_num, emap.encounter_num, f.encounter_ide,
45      f.start_date,
46      f.end_date,
47      f.provider_id,
48      f.modifier_cd,
49      f.instance_num,
50      f.update_date,
51      sysdate as import_date, up.upload_id, :download_date, up.source_cd
52from observation_fact_bsr f
53  join bsr_encounter_mapping emap
54    on f.encounter_ide = emap.encounter_ide
55  join mrn_patient_mappings pmap
56    -- patient_ide's from BSR can have leading '0's, the pmap does not
57    on f.patient_ide = pmap.MRN
58     , NightHeronData.upload_status up
59where up.upload_id = :upload_id
60;
61
62
63/* For this upload of data, check primary key constraints. */
64alter table observation_fact_upload
65  enable constraint observation_fact_pk
66  /* TODO: log errors ... ? #2117 */
67  ;
68
69/* Summary stats */
70update NightHeronData.upload_status
71  set loaded_record = (select count(*) from observation_fact_upload)
72    , no_of_record = (select count(*) from observation_fact_bsr)
73  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.