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_uhc.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: 6.6 KB
Line 
1/** test_uhc.sql -- check i2b2 ETL results vs 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 are in deid
8select concept_cd from BlueHeronData.observation_fact where 1 = 0;
9
10--Make sure we can talk to epic
11select pat_id from clarity.patient@id where 1 = 0;
12
13-- Check that at least some data is loaded.
14select case when fact_uploads > 0 then 1
15            else 1/0
16      end as some_data_loaded from (
17select count(*) fact_uploads from BlueHeronData.upload_status where load_status = 'OK'
18);
19
20/** Create mrn_patient_mapping
21TODO: Ask Dan.  Is this acceptable?  Not sure how best to create this mapping
22for the test.
23*/
24create or replace view mrn_patient_mappings as
25select patient_ide as MRN, patient_num, patient_ide_source
26from NightHeronData.patient_mapping@nheron pmap
27where pmap.patient_ide_status = 'A' and pmap.patient_ide_source like 'SMS@%';
28
29
30/*******************************************************************************
31Diagnosis->MDC (Major Diagnostic Category)
32*******************************************************************************/
33select case when source_num=i2b2_num then 1 else 1/0 end as uhc_diag_cat_match_source from(
34  with
35  i2b2_query as(
36    select count(*) i2b2_num from(
37    /*
38    The following is the query generated by i2b2 (blueherondata.qt_query_master)
39    */
40      SELECT
41          patient_num
42      FROM BlueHerondata.observation_fact
43      WHERE concept_cd IN
44        (SELECT concept_cd
45        FROM BlueHerondata.concept_dimension
46        WHERE concept_path LIKE '\i2b2\UHC\Diagnosis\Major Diagnostic Category\%'
47        )
48      GROUP BY patient_num
49      HAVING COUNT(*) >= 1
50    )
51  ),
52  source_query as (
53    select count(*) source_num from(
54      select
55        uenc.patientid
56      from
57        uhc.encounter uenc
58      join mrn_patient_mappings pmap
59      on uenc.patientid=pmap.mrn
60      where uenc.msdrgmdc is not null
61    )
62  )
63  select
64    source_num, i2b2_num
65  from source_query, i2b2_query);
66
67
68/*******************************************************************************
69Demographics->Race
70*******************************************************************************/
71select case when source_num=i2b2_num then 1 else 1/0 end as uhc_race_match_source 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      */
77      SELECT
78        patient_num
79      FROM BlueHerondata.observation_fact
80      WHERE concept_cd IN
81        (SELECT concept_cd
82        FROM BlueHerondata.concept_dimension
83        WHERE concept_path LIKE '\i2b2\UHC\Demographics\Race\%'
84        )
85      GROUP BY patient_num
86      HAVING COUNT(*) >= 1
87    )
88  ),
89  source_query as(
90    select count(*) source_num from(
91      select
92        uenc.patientid
93      from
94        uhc.encounter uenc
95      join mrn_patient_mappings pmap
96      on uenc.patientid=pmap.mrn
97      where uenc.race is not null
98    )
99  )
100  select
101    source_num, i2b2_num
102  from source_query, i2b2_query);
103
104
105/*******************************************************************************
106Admission->ICU Days
107*******************************************************************************/
108select case when source_num=i2b2_num then 1 else 1/0 end as uhc_icu_days_match_source from(
109  with i2b2_query as(
110    select count(*) i2b2_num from(
111      /*
112      The following is the query generated by i2b2 (blueherondata.qt_query_master)
113      */
114      SELECT
115        patient_num
116      FROM BlueHerondata.observation_fact
117      WHERE concept_cd IN
118        (SELECT concept_cd
119        FROM BlueHerondata.concept_dimension
120        WHERE concept_path LIKE '\i2b2\UHC\Admission\Length of Stay\ICU Days\%'
121        )
122      GROUP BY patient_num
123      HAVING COUNT(*) >= 1
124    )
125  ),
126  source_query as(
127    select count(*) source_num from(
128      select
129        uenc.patientid
130      from
131        uhc.encounter uenc
132      join mrn_patient_mappings pmap
133      on uenc.patientid=pmap.mrn
134      where uenc.icudays is not null
135    )
136  )
137  select
138    source_num, i2b2_num
139  from source_query, i2b2_query);
140
141
142/*******************************************************************************
143Admission->Readmission
144*******************************************************************************/
145select case when source_num=i2b2_num then 1 else 1/0 end as uhc_readmit_match_source from(
146  with i2b2_query as(
147    select count(*) i2b2_num from(
148      /*
149      The following is the query generated by i2b2 (blueherondata.qt_query_master)
150      */
151      SELECT
152        patient_num
153      FROM BlueHerondata.observation_fact
154      WHERE concept_cd IN
155        (SELECT concept_cd
156        FROM BlueHerondata.concept_dimension
157        WHERE concept_path LIKE '\i2b2\UHC\Admission\Days to Readmit\%'
158        )
159      GROUP BY patient_num
160      HAVING COUNT(*) >= 1
161    )
162  ),
163  source_query as(
164    select count(*) source_num from(
165      select
166        uenc.patientid
167      from
168        uhc.encounter uenc
169      join mrn_patient_mappings pmap
170      on uenc.patientid=pmap.mrn
171      join uhc.readmission uread
172      on uenc.recordid=uread.recordid
173    )
174  )
175  select
176    source_num, i2b2_num
177  from source_query, i2b2_query);
178
179 
180/*******************************************************************************
181Procedure->CCSICD9Procedure
182*******************************************************************************/
183select case when source_num=i2b2_num then 1 else 1/0 end as uhc_icd9proc_match_source from(
184  with i2b2_query as(
185    select count(*) i2b2_num from(
186      /*
187      The following is the query generated by i2b2 (blueherondata.qt_query_master)
188      */
189      SELECT
190        patient_num
191      FROM BlueHerondata.observation_fact
192      WHERE concept_cd IN
193        (SELECT concept_cd
194        FROM BlueHerondata.concept_dimension
195        WHERE concept_path LIKE '\i2b2\UHC\Procedures\CCS ICD9-CM Procedures\%'
196        )
197      GROUP BY patient_num
198      HAVING COUNT(*) >= 1
199      )
200  ),
201  source_query as(
202    select count(*) source_num from(
203      select
204        distinct(uenc.patientid)
205      from
206        uhc.encounter uenc
207      join mrn_patient_mappings pmap
208      on uenc.patientid=pmap.mrn
209      join uhc.procedure proc
210      on uenc.recordid=proc.recordid
211      where proc.ccsicd9cmproccode is not null
212    )
213  )
214  select
215    source_num, i2b2_num
216  from source_query, i2b2_query);
Note: See TracBrowser for help on using the repository browser.