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/test_medication_modifiers.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: 8.1 KB
Line 
1/** test_medication_modifiers.sql -- check i2b2 ETL results vs clarity source data.*/
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--Make sure we can talk to epic
8select pat_id from clarity.patient@id where 1 = 0;
9
10-- Check that at least some data is loaded.
11select case when fact_uploads > 0 then 1
12            else 1/0
13      end as some_data_loaded from (
14select count(*) fact_uploads from BlueHeronData.upload_status where load_status = 'OK'
15);
16
17
18/*******************************************************************************
19Dispensed Medication Modifier Tests:
20The test here is to verify that the number of patients that i2b2 returns when
21the user selects the "dispensed medications" modifier matches what we see in
22clarity.  So, we find out how many distinct patients we have an order for in
23clarity and compare it with the results of the auto-generated query from i2b2
24(run against blueherondata).  We get the generated query from
25blueheronmetadata.qt_query_master.
26*******************************************************************************/
27select case when (i2b2_num / clarity_num ) >= .95 then 1 else 1/0 end as med_disp_i2b2_match_clarity from(
28  with i2b2_query as
29    (select count(*) i2b2_num from
30      /*
31      The following is the query generated by i2b2 (blueherondata.qt_query_master)
32      when we select the top-level dispensed medication modifier.
33      */
34      (SELECT
35        patient_num
36      FROM BlueHerondata.observation_fact
37      WHERE concept_cd IN
38        (SELECT concept_cd
39        FROM BlueHerondata.concept_dimension
40        WHERE concept_path LIKE '\i2b2\Medications\%'
41        )
42      AND ( (MODIFIER_CD IN
43        (SELECT MODIFIER_CD
44        FROM BlueHerondata.MODIFIER_DIMENSION
45        WHERE MODIFIER_PATH LIKE '\Medication\Dispensed\%'
46        )) )
47      GROUP BY patient_num
48      HAVING COUNT(*) >= 1)),
49  clarity_query as
50    (select count(distinct com.pat_id) clarity_num
51      from
52        clarity.order_disp_meds@id codm
53      join
54        clarity.order_med@id com on
55        com.order_med_id=codm.order_med_id
56      )
57  select
58    clarity_num, i2b2_num
59  from clarity_query, i2b2_query);
60
61
62/*******************************************************************************
63Historical Medication Tests:
64The test here is to verify that the number of patients that i2b2 returns when
65the user selects the "historical medications" modifier matches what we see in
66clarity.  So, we find out how many distinct patients we have an historical order
67for in clarity and compare it with the results of the auto-generated query from
68i2b2 (run against blueherondata).  We get the generated query from
69blueheronmetadata.qt_query_master. 
70*******************************************************************************/
71select case when (i2b2_num / clarity_num ) >= .98 then 1 else 1/0 end as med_hist_i2b2_match_clarity from(
72  with i2b2_query as
73    (select count(*) i2b2_num from(
74      /*
75      The following is the query generated by i2b2 (blueherondata.qt_query_master)
76      when we select the top-level historical medication modifier.
77      */
78      SELECT
79        patient_num
80      FROM BlueHerondata.observation_fact
81      WHERE concept_cd IN
82        (SELECT concept_cd
83        FROM BlueHerondata.concept_dimension
84        WHERE concept_path LIKE '\i2b2\Medications\%'
85        )
86      AND ( (MODIFIER_CD IN
87        (SELECT MODIFIER_CD
88        FROM BlueHerondata.MODIFIER_DIMENSION
89        WHERE MODIFIER_PATH LIKE '\Medication\Historical\%'
90        )) )
91      GROUP BY patient_num
92      HAVING COUNT(*) >= 1)),
93  clarity_query as
94    (select count(*) clarity_num from (
95      select
96        distinct( com.pat_id )
97      from
98        clarity.order_med@id com
99      where
100        --Note the magic number for historical meds.  Refer CLARITY.zc_order_class
101        com.order_class_c=3
102      ))
103  select
104    clarity_num, i2b2_num
105  from clarity_query, i2b2_query);
106
107/*******************************************************************************
108"Other" Medication Modifier Tests:
109The test here is to verify that the number of patients that i2b2 returns when
110the user selects the "other medication order" modifier matches what we see in
111clarity.  So, we find out how many distinct patients we have a non-historical
112order for in clarity and compare it with the results of the auto-generated query
113from i2b2 (run against blueherondata).  We get the generated query from
114blueheronmetadata.qt_query_master. 
115*******************************************************************************/
116select case when (i2b2_num / clarity_num ) >= .98 then 1 else 1/0 end as med_other_i2b2_match_clarity from(
117  with i2b2_query as
118    (select count(*) i2b2_num from(
119    /*
120    The following is the query generated by i2b2 (blueherondata.qt_query_master)
121    when we select the top-level historical medication modifier.
122    */
123    SELECT
124      patient_num
125    FROM BlueHerondata.observation_fact
126    WHERE concept_cd IN
127      (SELECT concept_cd
128      FROM BlueHerondata.concept_dimension
129      WHERE concept_path LIKE '\i2b2\Medications\%'
130      )
131    AND ( (MODIFIER_CD IN
132      (SELECT MODIFIER_CD
133      FROM BlueHerondata.MODIFIER_DIMENSION
134      WHERE MODIFIER_PATH LIKE '\Medication\Other Orders\%'
135      )) )
136    GROUP BY patient_num
137    HAVING COUNT(*) >= 1)),
138  clarity_query as
139    (select count(*) clarity_num from (
140      select
141        distinct( com.pat_id )
142      from
143        clarity.order_med@id com
144      where
145        --Refer to CLARITY.zc_order_class
146        com.order_class_c is null or com.order_class_c!=3
147      ))
148  select
149    clarity_num, i2b2_num
150  from clarity_query, i2b2_query);
151
152
153/*******************************************************************************
154Inpatient/Outpatient Modifier Tests:
155*******************************************************************************/
156select
157  case when ((i2b2_num_inp/clarity_num_inp) >= .95 and (i2b2_num_outp/clarity_num_outp) >= .95) then 1
158    else 1/0 end as med_ambulatory_vs_inpatient from
159    (
160    with
161    i2b2_query_inp as
162      (select count(*) i2b2_num_inp from(
163      --The following is the query generated by i2b2 (blueherondata.qt_query_master)
164      SELECT
165        patient_num
166      FROM BlueHerondata.observation_fact
167      WHERE concept_cd IN
168        (SELECT concept_cd
169        FROM BlueHerondata.concept_dimension
170        WHERE concept_path LIKE '\i2b2\Medications\%'
171        )
172      AND ( (MODIFIER_CD IN
173        (SELECT MODIFIER_CD
174        FROM BlueHerondata.MODIFIER_DIMENSION
175        WHERE MODIFIER_PATH LIKE '\Medication\Inpatient\%'
176        )) )
177      GROUP BY patient_num
178      HAVING COUNT(*) >= 1
179      )),
180    i2b2_query_outp as
181      (select count(*) i2b2_num_outp from(
182      --The following is the query generated by i2b2 (blueherondata.qt_query_master)
183      SELECT
184        patient_num
185      FROM BlueHerondata.observation_fact
186      WHERE concept_cd IN
187        (SELECT concept_cd
188        FROM BlueHerondata.concept_dimension
189        WHERE concept_path LIKE '\i2b2\Medications\%'
190        )
191      AND ( (MODIFIER_CD IN
192        (SELECT MODIFIER_CD
193        FROM BlueHerondata.MODIFIER_DIMENSION
194        WHERE MODIFIER_PATH LIKE '\Medication\Outpatient\%'
195        )) )
196      GROUP BY patient_num
197      HAVING COUNT(*) >= 1
198      )),
199    clarity_query_inp as
200      (select count(*) clarity_num_inp from (
201        select
202          distinct( com.pat_id )
203        from
204          clarity.order_med@id com
205        where com.ordering_mode = 'Inpatient' or com.ordering_mode_c = 2
206      )),
207    clarity_query_outp as
208      (select count(*) clarity_num_outp from (
209        select
210          distinct( comb.pat_id )
211        from
212          clarity.order_med@id comb
213        where ( comb.ordering_mode = 'Outpatient' or comb.ordering_mode_c = 1 )
214      ))
215    select
216      i2b2_num_inp, i2b2_num_outp, clarity_num_inp, clarity_num_outp
217    from i2b2_query_inp, i2b2_query_outp, clarity_query_inp, clarity_query_outp );
Note: See TracBrowser for help on using the repository browser.