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/idx_demographics_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: 3.2 KB
Line 
1/* idx_demographics_load.sql -- load IDX demographic data into identified 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         http://informatics.kumc.edu/work/wiki/ClinicIdxSource
9
10patterned after epic_facts_load.sql
11
12*/
13select HOSPITAL_MEDICAL_RECORD from "&&kupi".idx_table where 1 = 0;
14select concept_cd from observation_fact_procedure where 1 = 0;
15
16/* Check for access to metadata in the deid DB. */
17select C_KEY from BLUEHERONMETADATA.schemes@deid where 1=0;
18
19
20truncate table observation_fact_upload;
21whenever sqlerror continue;
22alter table observation_fact_upload
23  disable constraint observation_fact_pk;
24whenever sqlerror exit;
25
26/** Demographics. todo. See #406
27 */
28
29
30/*****
31 * Per-patient flags: HICTR/Pioneers participant, email on file
32
33for testing:
34variable upload_id number
35exec :upload_id := 1;
36 */
37
38insert into observation_fact_upload (
39      concept_cd, valtype_cd, tval_char,
40      patient_num, encounter_num, sub_encounter,
41      start_date,
42      end_date,
43      provider_id,
44      modifier_cd,
45      instance_num,
46      update_date,
47      import_date, upload_id, download_date, sourcesystem_cd)
48select
49      f.concept_cd, f.valtype_cd, f.tval_char,
50      pmap.patient_num, emap.encounter_num, f.encounter_ide,
51      f.start_date,
52      f.end_date,
53      f.provider_id,
54      f.modifier_cd,
55      f.instance_num,
56      f.update_date,
57      sysdate as import_date, up.upload_id, :download_date, up.source_cd
58from (select * from observation_fact_hictr
59      union all
60      select * from observation_fact_email) f
61  join idx_encounter_mapping emap
62    on f.encounter_ide = emap.encounter_ide
63    join mrn_patient_mappings pmap
64    on f.patient_ide = pmap.MRN
65      , NightHeronData.upload_status up
66where up.upload_id = :upload_id
67  -- chunking is overkill here.
68  -- and f.part = :part
69;
70
71
72insert into etl_test_values (test_value, test_domain, test_name, result_id, result_date)
73with test_key as (
74  select 'Demographics' test_domain,
75  'hictr_mrn_leading_zero' test_name from dual
76  )
77--Below line for getting the test description included in curated data
78--select * from test_key natural join etl_tests;
79, test_values as (
80  select count(*) test_value, test_key.* from (
81    select pm.patient_ide patient_ide
82    from observation_fact_upload obs
83    join nightherondata.patient_mapping pm on pm.patient_num = obs.patient_num
84    where obs.concept_cd = 'HICTR_PARTICIPANT:yes'
85    and pm.patient_ide_source = 'SMS@kumed.com'
86    and length(pm.patient_ide) < 7
87    ), test_key
88  )
89select test_value, test_domain, test_name, sq_result_id.nextval, sysdate
90from test_values
91;
92commit;
93
94   
95/** Summary stats.
96
97Report how many rows are dropped when joining on patient_ide and encounter_id.
98Subsumes check for null :part (#789).
99*/
100update NightHeronData.upload_status
101  set loaded_record = (select count(*) from observation_fact_upload)
102    , no_of_record = (select count(*) from observation_fact_hictr) +
103                     (select count(*) from observation_fact_email)
104  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.