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_enc_vitals_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: 9.6 KB
Line 
1/*-- Transforming EPIC Encounter Vitals -- epic_enc_vitals_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
6Based on epic_orders_transform.sql & enc_vitals_etc.sql
7*/
8
9-- Check that we're connected to an Epic CLARITY database.
10select pat_id from CLARITY.patient where 1 = 0;
11
12/*
13-- eyeballing data
14select count(*) from clarity.pat_enc where
15BP_SYSTOLIC is not null
16or BP_DIASTOLIC is not null
17or TEMPERATURE is not null
18or PULSE is not null
19or WEIGHT is not null
20or HEIGHT is not null
21or RESPIRATIONS is not null
22or LMP_DATE is not null
23or LMP_OTHER_C is not null
24or HEAD_CIRCUMFERENCE is not null
25or BMI is not null
26or BSA is not null
27;
28desc clarity.pat_enc;
29
30select * from CLARITY.ZC_LMP_OTHER zlo;
31select count(*) from clarity.pat_enc
32where LMP_OTHER_C is not null;
33
34select count(*)
35from clarity.pat_enc cpe
36join CLARITY.ZC_LMP_OTHER zlo
37on zlo.LMP_OTHER_C = cpe.LMP_OTHER_C;
38
39-- This drop is due to value 0 that does not have any corresponding code.
40-- TODO: Test that we're dropping an insignificant proportion of records.
41
42select cpe.LMP_OTHER_C, TITLE
43from clarity.pat_enc cpe
44join CLARITY.ZC_LMP_OTHER zlo
45on zlo.LMP_OTHER_C! = cpe.LMP_OTHER_C;
46
47select count(*) from clarity.pat_enc cpe
48where cpe.LMP_OTHER_C = '0';
49select * from CLARITY.ZC_LMP_OTHER;
50
51*/
52
53/* For height below, we use a regex to pull out the numerical values.  Check our
54regex here to make sure we get most of the cases.
55*/
56--how many fit the pattern?
57select case when matching_prcnt < 99 then 1/0 else 1 end height_regex_match from (
58  with
59  matching_count as(
60    select count(*) cnt from(
61      select regexp_substr(pe.height, '[0-9]+{1}'' ([0-9])?{1,2}(.[0-9]+)?') num_str
62      from CLARITY.Pat_enc pe
63      where pe.HEIGHT is not null
64      )
65    where num_str is not null
66    ),
67  total_count as (
68    select count(*) cnt from (
69      select pe.height
70      from CLARITY.Pat_enc pe
71      where pe.HEIGHT is not null
72      )
73    )
74  select round(100*(matching_count.cnt / total_count.cnt), 2) matching_prcnt
75  from matching_count, total_count
76  );
77/* To find missing ones...
78select height from(
79  select regexp_substr(pe.height, '[0-9]+{1}'' ([0-9])?{1,2}(.[0-9]+)?') num_str, pe.*
80  from CLARITY.Pat_enc pe
81  where pe.HEIGHT is not null
82  )
83where num_str is null;
84*/
85
86
87/*View to pivot the observations in pat_enc*/
88create or replace view encounter_vitals_pivot as
89(
90/* This is the patient's Body Mass Index,
91which is calculated based on the recorded height and weight. */
92select
93 pe.pat_enc_csn_id encounter_ide
94 , 'BMI' column_name
95 , 'N' valtype_cd
96 , 'E' tval_char
97 , pe.BMI nval_num
98 , pe.contact_date start_date
99 , pe.contact_date end_date
100from CLARITY.Pat_enc pe
101where pe.BMI is not NULL
102
103) union all (
104
105select * from (
106  with
107  pat_bmi as (
108    select
109      pat.pat_id, pe.pat_enc_csn_id, pe.contact_date,
110      pat.sex_c, pe.bmi,
111      months_between(coalesce(pat.death_date, pe.contact_date), pat.birth_date) agemo
112    from clarity.pat_enc pe
113    join clarity.patient pat on pe.pat_id = pat.pat_id
114    where pe.bmi is not null
115    ),
116  deltas as (
117    select p.pat_enc_csn_id, p.contact_date, cdc.*, abs(p.bmi - cdc.bmi) bmi_delta
118    from pat_bmi p
119    /* Sex codes between CDC and Clarity are opposite (1,2). Age in months is
120    rounded to the nearest .5 in the CDC data.
121    */
122    join cdc.bmi_percentiles cdc on cdc.agemos = round(p.agemo * 2)/2 and
123     ((p.sex_c = 1 and cdc.sex = 2) or
124      (p.sex_c = 2 and cdc.sex = 1))
125    ),
126  enc_bmi as (
127    select d2.* from (
128      select min(d.bmi_delta) min_bmi_delta, d.pat_enc_csn_id, d.contact_date
129      from deltas d
130      group by d.pat_enc_csn_id, d.contact_date
131      ) md
132    join deltas d2 on d2.pat_enc_csn_id = md.pat_enc_csn_id
133      and d2.bmi_delta = md.min_bmi_delta
134    )
135  select
136   enc_bmi.pat_enc_csn_id encounter_ide
137   , 'BMI_PERCENTILE' column_name
138   , 'N' valtype_cd
139   , 'E' tval_char
140   , enc_bmi.percentile nval_num
141   , enc_bmi.contact_date start_date
142   , enc_bmi.contact_date end_date
143  from enc_bmi
144  ) 
145) union all (
146
147/* This is the patient's Body Surface Area,
148which is calculated based on the recorded height and weight. */
149select
150 pe.pat_enc_csn_id encounter_ide
151 , 'BSA' column_name
152 , 'N' valtype_cd
153 , 'E' tval_char
154 , pe.BSA nval_num
155 , pe.contact_date start_date
156 , pe.contact_date end_date
157 /* units? wikipedia etc. say m^2 */
158from CLARITY.Pat_enc pe
159 where pe.bsa is not null
160 
161) union all (
162
163/* The patient's Head Circumference as recorded during this encounter.
164This item will contain data only for patients younger than the age specified
165in Miscellaneous Configuration. Note: This value is stored in centimeters.  */
166select
167 pe.pat_enc_csn_id encounter_ide
168 , 'HEAD_CIRCUMFERENCE' column_name
169 , 'N' valtype_cd
170 , 'E' tval_char
171 , pe.HEAD_CIRCUMFERENCE nval_num
172 , pe.contact_date start_date
173 , pe.contact_date end_date
174from CLARITY.Pat_enc pe
175 where pe.HEAD_CIRCUMFERENCE is not null
176 
177) union all (
178
179/*Clarity Doc: The category value associated
180with alternative information entered in the LMP field
181of an clinical system encounter regarding the patient's OB/GYN Status */
182select pe.pat_enc_csn_id encounter_ide
183 , 'LMP'||zlo.lmp_other_c as column_name
184 , '@' valtype_cd
185 , '@' tval_char
186 , to_number(null) nval_num
187 , nvl(pe.lmp_date,pe.contact_date) start_date
188 , nvl(pe.lmp_date,pe.contact_date) end_date
189from CLARITY.Pat_enc pe
190join
191CLARITY.ZC_LMP_OTHER zlo
192on zlo.lmp_other_c = pe.lmp_other_c
193where pe.lmp_other_c is not NULL
194
195) union all (
196
197/* The patient's respiration rate as recorded during this encounter. */
198select
199 pe.pat_enc_csn_id encounter_ide
200 , 'RESPIRATIONS' column_name
201 , 'N' valtype_cd
202 , 'E' tval_char
203 , pe.RESPIRATIONS nval_num
204 , pe.contact_date start_date
205 , pe.contact_date end_date
206from CLARITY.Pat_enc pe
207 where pe.RESPIRATIONS is not null
208 
209) union all (
210
211/* The patient's height as recorded during this encounter. This field is a
212string and may contain indicators for feet and/or inches. */
213select encounter_ide, column_name, valtype_cd, tval_char,
214(to_number(substr(num_str, 1, instr(num_str, '''')-1)) * 12 + substr(num_str, instr(num_str, ' ') + 1)) * 2.54 nval_num,
215start_date, end_date
216from(
217  select
218   pe.pat_enc_csn_id encounter_ide,
219   'HEIGHT' column_name,
220   'N' valtype_cd, 'E' tval_char,
221   -- 9' 99.99"
222   regexp_substr(pe.height, '[0-9]+{1}'' ([0-9])?{1,2}(.[0-9]+)?') num_str,
223   pe.contact_date start_date,
224   pe.contact_date end_date
225  from CLARITY.Pat_enc pe
226   where pe.HEIGHT is not null
227  )
228where num_str is not null
229) union all (
230
231/* The patient's weight as recorded during this encounter.
232Note: This field is stored in ounces regardless of how
233the weight was entered. Divide this number by 16 to report
234the patient's weight in pounds. */
235select
236 pe.pat_enc_csn_id encounter_ide
237 , 'WEIGHT' column_name
238 , 'N' valtype_cd
239 , 'E' tval_char
240 , pe.WEIGHT nval_num
241 , pe.contact_date start_date
242 , pe.contact_date end_date
243from CLARITY.Pat_enc pe
244 where pe.WEIGHT is not null
245
246) union all (
247
248/* The patient's pulse taken during this encounter. */
249select
250 pe.pat_enc_csn_id encounter_ide
251 , 'PULSE' column_name
252 , 'N' valtype_cd
253 , 'E' tval_char
254 , pe.PULSE nval_num
255 , pe.contact_date start_date
256 , pe.contact_date end_date
257from CLARITY.Pat_enc pe
258 where pe.PULSE is not null
259 
260) union all (
261
262/* The patient's temperature taken during this encounter.
263This value is in degrees Fahrenheit regardless
264of how the temperature reading was entered.  */
265select
266 pe.pat_enc_csn_id encounter_ide
267 , 'TEMPERATURE' column_name
268 , 'N' valtype_cd
269 , 'E' tval_char
270 , pe.TEMPERATURE nval_num
271 , pe.contact_date start_date
272 , pe.contact_date end_date
273from CLARITY.Pat_enc pe
274 where pe.TEMPERATURE is not null
275
276) union all (
277
278select
279 pe.pat_enc_csn_id encounter_ide
280 , 'BP_SYSTOLIC' column_name
281 , 'N' valtype_cd
282 , 'E' tval_char
283 , pe.BP_SYSTOLIC nval_num
284 , pe.contact_date start_date
285 , pe.contact_date end_date
286from CLARITY.Pat_enc pe
287 where pe.BP_SYSTOLIC is not null
288 
289) union all (
290
291select
292 pe.pat_enc_csn_id encounter_ide
293 , 'BP_DIASTOLIC' column_name
294 , 'N' valtype_cd
295 , 'E' tval_char
296 , pe.BP_DIASTOLIC nval_num
297 , pe.contact_date start_date
298 , pe.contact_date end_date
299from CLARITY.Pat_enc pe
300 where pe.BP_DIASTOLIC is not null
301);
302-- TODO: Test the number of observations that we are loading vs. total
303
304
305create or replace view kuh_encounter_scheme as
306select
307  'KUH|PAT_ENC:' c_key,
308  'KUH|PAT_ENC' c_name,
309  'Vitals etc from patient encounters, as used in Epic deployed at kumed.com' c_description,
310  '\i2b2\VitalSignMeasure\' concept_path
311  from dual;
312-- select * from kuh_encounter_scheme
313
314/*
315-- eyeball
316select * from
317encounter_vitals_pivot pivot
318where NOT regexp_like(pivot.nval_num,'^[[:digit:]]+$')
319*/
320
321
322create or replace view observation_fact_enc_vitals as
323select
324  pe.pat_id patient_ide
325  , mod(ora_hash(pivot.encounter_ide), &&heron_etl_chunks)+1 as part
326  , pivot.start_date
327  , pivot.end_date
328  , to_char(pivot.encounter_ide) encounter_ide
329  , scheme.c_key || pivot.column_name concept_cd
330  , pivot.valtype_cd
331  , pivot.tval_char
332  , pivot.nval_num
333  , '@' PROVIDER_ID
334  , null valueflag_cd
335  , null units_cd
336  , null location_cd
337  , '@' modifier_cd
338  , to_number(null) confidence_num
339  , to_date(null) update_date
340  , pe.pat_enc_csn_id instance_num
341from
342  encounter_vitals_pivot pivot,
343  kuh_encounter_scheme scheme,
344  clarity.pat_enc pe
345where pe.pat_enc_csn_id = pivot.encounter_ide;
Note: See TracBrowser for help on using the repository browser.