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/epic_microbiology_transform.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: 7.8 KB
Line 
1/* epic_microbiology_transform -- harvest microbiology results into fact table
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
7Ref:
8
9  * Clarity Data Dictionary - Epic 2012
10    Catalog (Last updated: 07/14/12)
11    https://galaxy.epic.com/Search/GetFile?url=7900!44!100!1770229
12
13*/
14
15create or replace view microbiology_sensitivity as
16  select to_char(cop.pat_enc_csn_id) as encounter_ide    ,
17    cop.pat_id as patient_ide                            ,
18    cop.proc_id                                          ,
19    'KUMC|Micro|Org|Med:'||cop.proc_id||'|'||coo.organism_id||'|'||cza.antibiotic_c as concept_cd,
20      case
21      when cop.ordering_date is null then cop.order_inst
22      else cop.ordering_date
23    end start_date,
24    'MicroSensitivity:'||czs.suscept_c as modifier_cd,
25    --! instance num provides uniqueness for duplicates that could exist in
26    --! order_results and order_sensitivity
27    (cor.order_proc_id*1000 +cor.line)*100 +cos.line as instance_num,
28    cor.result_date as end_date,
29    cor.result_date as update_date,
30    mod(cor.pat_enc_csn_id, &&heron_etl_chunks)+1 as part,
31    fconst.* -- add in default values for provider_id, valtype_cd etc.
32from clarity.order_proc cop
33join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
34join CLARITY.order_results cor on cor.order_proc_id = cop.order_proc_id
35join CLARITY.order_sensitivity cos on cos.order_proc_id = cop.order_proc_id
36join CLARITY.clarity_organism coo on coo.organism_id=cos.organism_id
37join CLARITY.zc_antibiotic cza on cos.antibiotic_c=cza.antibiotic_c
38join clarity.zc_suscept czs on cos.suscept_c=czs.suscept_c,
39discrete_fact_constants fconst
40where cop.pat_enc_csn_id is NOT NULL;
41
42/*select count(*) from microbiology_sensitivity;
43  --takes 165 seconds on production data*/
44
45
46/**
47 * Test that there are no duplicates for:
48 * encounter_ide, concept_cd, start_date, modifier_cd, instance_num
49 * as to no violate the uniqueness constraint.
50 *
51select (case when count(*) > 0 then 1/0 else 1 end) as no_duplicates
52from (select count(*), encounter_ide, concept_cd, start_date, modifier_cd, instance_num
53      from microbiology_sensitivity
54      group by encounter_ide, concept_cd, start_date, modifier_cd, instance_num
55      having count(*) > 1
56     )
57 */
58;
59
60/************ micro_negative_results -- build facts for microbiology negative results.**************/
61create or replace view microbiology_negativeresults as
62  select to_char(cop.pat_enc_csn_id) as encounter_ide    ,
63    cop.pat_id as patient_ide                            ,
64    'KUMC|MicroNegative:'||cop.proc_id||'|'|| (case when cop.specimen_source_c is null
65                                              then '0'
66                                              else to_char(cop.specimen_source_c)
67                                              end) as concept_cd,
68      case
69      when cop.ordering_date is null then cop.order_inst
70      else cop.ordering_date
71    end start_date,
72    '@' as modifier_cd,
73    --! instance num provides uniqueness for duplicates that could exist in
74    --! order_results and order_sensitivity
75    (cor.order_proc_id*1000 +cor.line)*100 as instance_num,
76    cor.result_date as end_date,
77    cor.result_date as update_date,
78    mod(cor.pat_enc_csn_id, &&heron_etl_chunks)+1 as part,
79    fconst.* -- add in default values for provider_id, valtype_cd etc.
80from clarity.order_proc cop
81join clarity.order_results cor on cop.order_proc_id=cor.order_proc_id
82join clarity.clarity_component cccc on cor.component_id=cccc.component_id
83join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
84left join clarity.zc_specimen_source czs on cop.specimen_source_c=czs.specimen_source_c,
85discrete_fact_constants fconst
86
87-- Must remain in sync with where clause in micro_negative_results view
88-- [epic_concepts_load.sql]
89--TODO remove dependency or delegate dependency to shared location
90where cop.order_type_c=3
91and   (upper(cor.ord_value) like 'NEG%'
92     or upper(cor.ord_value) like '%NONE%'
93     or upper(cor.ord_value) like 'NOS%'
94     or upper(cor.ord_value) like 'NO %'
95     or upper(cor.ord_value) like '%NOT %'
96     or upper(cor.ord_value) like 'NEGATIVE%'       
97     or upper(cor.ord_value) like '%<15CFU%'
98     or upper(cor.ord_value) like 'NORMAL%'
99     )
100and cor.result_status_c=3
101and cor.component_id in
102  (select distinct component_id from microlab_component_whitelist)
103and cop.pat_enc_csn_id is NOT NULL;
104
105
106
107/************ micro_positiveresults -- build facts for microbiology positive results.**************/
108
109/* Procedure order ids that are microbiology results with sensitivity should not
110be included in positive results without sensitivity.
111
112Creating a table for distinct proc_ids from the microbiology results with
113sensitivity because if we use a sub-select to do this in the
114microbiology_positiveresults view, the microbiology_sensitivity gets scanned
115over and over again to find distinct proc_ids.  This didn't complete after days
116of running.
117*/
118whenever sqlerror continue;
119drop table sens_proc_ids;
120whenever sqlerror exit;
121
122create table sens_proc_ids as (
123  select distinct proc_id from  microbiology_sensitivity
124  )
125;
126
127create or replace view microbiology_positiveresults as
128select to_char(cop.pat_enc_csn_id) as encounter_ide    ,
129    cop.pat_id as patient_ide                          ,
130    cop.proc_id                                        ,
131    cop.description as copdescription                  ,
132    ceap.proc_name as ceapname                              ,
133    cop.order_proc_id                                  ,
134    (case when cop.specimen_source_c is null
135             then 'Specimen source not recorded '
136             else czs.name
137        end) as specimensourcename                    ,                   
138    (case when cop.specimen_source_c is null
139    then 0
140    else cop.specimen_source_c
141    end)  as  specimen_source_c                      ,
142    'KUMC|MicroPositive:'||cop.proc_id||'|'|| (case when cop.specimen_source_c is null
143                                              then '0'
144                                              else to_char(cop.specimen_source_c)
145                                              end) as concept_cd,
146      case
147      when cop.ordering_date is null then cop.order_inst
148      else cop.ordering_date
149    end start_date,
150    '@' as modifier_cd,
151    --! instance num provides uniqueness for duplicates that could exist in
152    --! order_results and order_sensitivity
153    (cor.order_proc_id*1000 +cor.line)*100 as instance_num,
154    cor.result_date as end_date,
155    cor.result_date as update_date,
156    mod(cor.pat_enc_csn_id, &&heron_etl_chunks)+1 as part,
157   fconst.* -- add in default values for provider_id, valtype_cd etc.
158from clarity.order_proc cop
159join clarity.clarity_eap  ceap on cop.proc_id=ceap.proc_id
160join CLARITY.order_results cor on cor.order_proc_id = cop.order_proc_id
161join clarity.clarity_component ccc on cor.component_id=ccc.component_id
162left join clarity.zc_specimen_source czs on cop.specimen_source_c=czs.specimen_source_c,
163discrete_fact_constants fconst
164where cop.proc_id not in
165(select proc_id from sens_proc_ids)-- removing micro sensitivity results from the positive results
166and cop.order_type_c=3 and cor.result_status_c=3
167and cop.proc_id not in(10211374,10212244)--removing mai complex sensitivity results and micro summary results
168and (upper(ord_value) like '%POSITIVE%' OR upper(ord_value) like 'POS' or upper(ord_value) like 'DETECTED%'
169or upper(ord_value) like '%MODERATE GROWTH%' or upper(ord_value) like '%LIGHT GROWTH%' or upper(ord_value) like '%HEAVY GROWTH%')
170or upper(ord_value) like '%Abnormal%'
171and cor.component_id in(select component_id from microlab_component_whitelist);
172
173
174
Note: See TracBrowser for help on using the repository browser.