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_orders_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: 5.8 KB
Line 
1 -- Transforming EPIC Procedure Orders -- epic_orders_transform.sql --
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*/
7-- Based on epic_i2b2_transform.sql
8-- TODO: Verify if all the observations are being loaded
9/* Check that we're connected to an Epic CLARITY database.
10   */
11select pat_id from CLARITY.patient where 1 = 0;
12
13/*
14 * Patient_dimension
15 *
16 * Note we include both the pat_id and the MRN from identity_id.
17 * Both are added to patient_mapping in epic_i2b2_load.sql.
18 */
19
20/*
21select count(*) from clarity.order_proc cop
22left outer join
23clarity.clarity_eap cce
24on cop.proc_id=cce.proc_id
25where cce.proc_id is NULL
26;
27-- 41870488 (all the rows in order_proc)
28-- 242841 rows
29*/
30
31/*
32These seem to be all the date fields that are of importance in order_proc
33order_time
34result_time
35proc_date
36PROC_START_TIME
37proc_ending_time
38
39select count(*) from clarity.order_proc where order_inst is NULL;
40-- 564536
41select count(*) from clarity.order_proc where ordering_date is NULL;
42-- 243187
43select count(*) from clarity.order_proc where ordering_date is NULL and proc_id is not null and order_inst is NULL;
44-- 0
45select count(*) from clarity.order_proc where result_time is NULL;
46-- 25755108
47
48select count(*) from clarity.order_proc where UPDATE_DATE is NULL;
49--0
50select count(distinct update_date) from clarity.order_proc;
512528
52
53select count(*) from clarity.order_proc where proc_end_time is NULL;
54-- 41797823
55*/
56
57/*
58select count(*) from clarity.order_proc where proc_id is NULL;
59-- 242841 which is 0.5% of the total number of orders 41870488
60
61select * from clarity.clarity_eap;
62*/
63
64/*
65Assumption: Either ordering_date or order_inst is always recorded.
66They do not seem to be recorded for proc orders that do not have a proc_id
67*/
68create or replace view observation_fact_orders as
69with prelim_view as (
70select cop.pat_id patient_ide
71  , mod(ora_hash(cop.pat_enc_csn_id), &&heron_etl_chunks)+1 as part
72  , case
73      when cop.ordering_date is null then cop.order_inst
74      else cop.ordering_date
75    end start_date
76  , case
77      when cop.ordering_date is null then cop.order_inst
78      else cop.ordering_date
79    end end_date
80  -- couldn't find a particular end time
81  , to_char(cop.pat_enc_csn_id) encounter_ide
82  , 'KUH|PROC_ID:'||cce.proc_id as concept_cd
83  , '@' valtype_cd
84  , '@' tval_char
85  , to_number(null) nval_num
86  , '@' provider_id
87  , order_proc_id instance_num
88  , null valueflag_cd
89  , null units_cd
90  , null location_cd
91  , to_number(null) confidence_num
92  , update_date update_date
93  , case
94      /* According to "Clarity Data Dictionary", order_proc.ordering_mode is
95      deprecated - look at order_proc_3.ordering_mode_c instead.
96        1 = Outpatient   
97        2 = Inpatient   
98      */
99      when cop3.ordering_mode_c = 2 then 'PROCORDERS:Inpatient'
100      when cop3.ordering_mode_c = 1 then 'PROCORDERS:Outpatient'
101      else 'PROCORDERS:Notrecorded'
102    end order_mod
103  from clarity.ORDER_PROC cop
104  join clarity.clarity_eap cce
105  on cce.proc_id = cop.proc_id
106  join clarity.order_proc_3 cop3
107  on cop.order_proc_id = cop3.order_id
108  where cop.pat_enc_csn_id is NOT NULL)
109  select prelim_view.*, '@' as modifier_cd from prelim_view
110  union all
111  select prelim_view.*, order_mod as modifier_cd from prelim_view
112;
113-- 50576398 rows in total
114-- 50575173 rows in total have pat_enc_csn_id
115-- 1225 rows have a NULL pat_enc_csn_id
116
117
118/*
119Testing for the assumption above that
120ordering_date or order_inst is always recorded.
121*/
122select
123case
124when count(*) > 0 then 1/0
125else 1
126end
127from observation_fact_orders
128where concept_cd='KUH|PROC_ID:' -- NULL proc_id
129or start_date is NULL
130or end_date is NULL ;
131
132
133/*
134Testing that a majority of observations are not missing
135pat_enc_csn_id i.e. encounter details.
136*/
137select
138case
139when (total_observ-loaded_observ)/total_observ * 100 > 0.1 then 1/0
140else 1
141end
142from (
143select (select count(*) 
144from observation_fact_orders) as loaded_observ ,
145(select count(*) 
146from clarity.ORDER_PROC cop
147  join clarity.clarity_eap cce
148  on cce.proc_id = cop.proc_id
149) as total_observ from dual)
150 ;
151
152-- 41627647 orders in order_proc have a proc_id
153-- 242841: 0.5% of the total number of orders 41870488 don't have proc_id
154-- That is exactly the number that doesn't has an ordering date
155-- 41627647 distinct encounter, concept_cd, patient_ide, modifier_cd combinations
156/*
157order_time
158result_time
159proc_date
160PROC_START_TIME
161proc_ending_time
162*/
163
164/*
165select distinct order_proc_id from clarity.order_proc cop where
166proc_id is NULL
167and
168cop.ordering_date is null
169and
170cop.order_inst is null
171;
172
173select count(*) from (
174select cop.pat_id patient_ide
175  -- , mod(ora_hash(cop.pat_enc_csn_id), &&heron_etl_chunks)+1 as part
176  , case
177      when cop.ordering_date is null then cop.order_inst
178      else cop.ordering_date
179    end start_date
180  , case
181      when cop.ordering_date is null then cop.order_inst
182      else cop.ordering_date
183    end end_date
184  -- couldn't find a particular end time
185  , to_char(cop.pat_enc_csn_id) encounter_ide
186  , 'KUH|PROC_ID:'||cce.proc_id as concept_cd
187  , '@' valtype_cd
188  , '@' tval_char
189  , to_number(null) nval_num
190  , '@' provider_id
191  , order_proc_id instance_num
192  , null valueflag_cd
193  , null units_cd
194  , null location_cd
195  , to_number(null) confidence_num
196  , update_date update_date
197  , '@' modifier_cd
198from clarity.ORDER_PROC cop
199  join clarity.clarity_eap cce
200  on cce.proc_id = cop.proc_id
201  )
202where concept_cd='KUH|PROC_ID:'
203 or start_date is NULL
204or end_date is NULL ;
205
206select * from observation_fact_orders where concept_cd LIKE 'KUH|PROC_ID:%';
207
208select count(*) from clarity.order_proc;
209*/
Note: See TracBrowser for help on using the repository browser.