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/flowsheet_concepts.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: 16.6 KB
Line 
1/* flowsheet_concepts.sql -- Relate flo_meas_id's to groupers and templates.
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
7Designed to run in a CLARITY instance.
8
9References:
10
11 Lesson 4: Flowsheet Data
12 Clarity Data Model - EpicCare Inpatient Spring 2008 Training Companion
13 https://userweb.epic.com/epiclib/epicdoc/EpicWiseSpr08/Clarity/Clarity%20Training%20Companions/CLR209%20Data%20Model%20-%20EpicCare%20Inpatient/04TC%20Flowsheet%20Data.doc
14
15In the concept hierarchy for nursing flowsheet observations:
16 - Flowsheets are grouped into buckets, manually (see flowsheet_buckets view).
17 - Flowsheets contain groups and/or measures.
18 - Groups contain measures.
19 - Measures may be scalar/atomic or Custom Lists.
20 - Custom Lists contain custom list choices.
21
22The display clues are:
23
24 + BUCKET :
25   + Flowsheet Display Name #9999
26     + 001-Group Name:
27          001-Scalar Measure Name
28        + 002-Custom List Measure Name >
29            01-FIRST CHOICE
30            02-SECOND CHOICE
31          003-Another Scalar Measure
32       002-Scalar Measure Directly On Flowsheet
33       003-Exercise Minutes/Week
34     + 004-Food Allergies >
35         01-PEANUTS
36         02-TREE NUTS
37         03-FISH
38
39relevant tables:
40ip_flwsht_meas - the data
41
42ip_flo_gp_data - generic info about a flowsheet measure
43
44if_flo_measure - the grouper to measure table.
45
46ip_flt_data - the templates names  767 templates defined, 495 KU specific
47
48ip_flt_comps - which groupers are on which templates. 
49
50-- 2924 distinct groups, 13421 distinct measures, 20896 total measures on groups. 
51select count(distinct flo_meas_id), count(distinct grp_flo_meas_id), count(*)
52from clarity.ip_flo_measure
53
54--implications of ADD_START_REMOVE_YN column in ifgd?  I can't find this start_remove concept anywhere.....
55
56SELECT TABLE_NAME, COLUMN_NAME
57FROM ALL_TAB_COLUMNS
58WHERE COLUMN_NAME LIKE 'TEMPLATE_ID'
59
60
61-- count the number of rows in ifc  6253 template-group/flow rows
62select count(*)
63from clarity.ip_flt_comps ifc
64
65select *
66from clarity.ip_flt_data
67order by template_name
68*/
69
70
71/* TODO: consider moving this to a CSV file or some such. */
72/* todo: put all the buckets at the same level. */
73create or replace view flowsheet_buckets as
74      select 2 depth, 'XXX' prefix, null super from dual
75union select 2, 'ED ', null from dual
76union select 3, 'KU AMB ', 'KU ' from dual
77union select 4, 'KU ED TMP NEURO ', 'KU ED ' from dual
78union select 4, 'KU ED TMP PEDS ', 'KU ED ' from dual
79union select 4, 'KU ED TMP RN ', 'KU ED ' from dual
80union select 3, 'KU ED ', 'KU ' from dual
81union select 4, 'KU GEN CARD EP ', 'KU GEN CARD ' from dual
82union select 4, 'KU GEN CARD HF ', 'KU GEN CARD ' from dual
83union select 3, 'KU GEN CARD ', 'KU ' from dual
84union select 3, 'KU GEN CC ', 'KU ' from dual
85union select 4, 'KU IP TMP NICU ', 'KU IP ' from dual
86union select 5, 'KU IP TMP OB DELIVERY ', 'KU IP TMP OB ' from dual
87union select 5, 'KU IP TMP OB INFANT ', 'KU IP TMP OB ' from dual
88union select 5, 'KU IP TMP OB LABOR ', 'KU IP TMP OB ' from dual
89union select 4, 'KU IP TMP OT ', 'KU IP ' from dual
90union select 4, 'KU IP TMP PEDS ', 'KU IP ' from dual
91union select 4, 'KU IP TMP PT ', 'KU IP ' from dual
92union select 4, 'KU IP TMP RN ', 'KU IP ' from dual
93union select 4, 'KU IP TMP RT ', 'KU IP ' from dual
94union select 4, 'KU IP TMP SLP ', 'KU IP ' from dual
95union select 3, 'KU IP ', 'KU ' from dual
96union select 3, 'KU ONC ', 'KU ' from dual
97union select 2, 'KU ', null from dual
98union select 2, 'MODEL ', null from dual
99union select 2, 'OB ', null from dual
100union select 2, 'OPHTH ', null from dual
101union select 2, 'SAMPLE ', null from dual
102union select 2, 'UKP ', null from dual
103union select 2, 'Z', null from dual
104union select 3, 'MODEL IP ', 'MODEL ' from dual
105union select 3, 'MODEL OR ', 'MODEL ' from dual
106union select 3, 'MODEL RESTRAINTS ', 'MODEL ' from dual
107union select 3, 'SAMPLE NICU ', 'SAMPLE ' from dual
108union select 3, 'SAMPLE OB ', 'SAMPLE ' from dual
109union select 3, 'SAMPLE PEDS ', 'SAMPLE ' from dual
110union select 3, 'SAMPLE PSYCH ', 'SAMPLE ' from dual
111union select 3, 'SAMPLE RESP ', 'SAMPLE ' from dual
112union select 4, 'KU GEN CC ', 'KU ' from dual
113union select 4, 'KU IP TMP OB ', 'KU IP ' from dual;
114
115/*
116Check for any mismatches:
117
118select *
119from flowsheet_buckets fb
120where super is not null
121  and super not in (
122    select prefix from flowsheet_buckets);
123*/
124
125
126/** kuh_flowsheet_scheme -- concept schemes for flowsheets
127 */
128create or replace view measure_scheme as
129select
130  'KUH|FLO_MEAS_ID:' c_key,
131  'KUH|FLO_MEAS_ID' c_name,
132  'FLO_MEAS_ID from O2, i.e. Epic as deployed at KUH' c_description
133  from dual;
134create or replace view choice_scheme as
135select
136  'KUH|FLO_MEAS_ID+LINE:' c_key,
137  'KUH|FLO_MEAS_ID+LINE' c_name,
138  'FLO_MEAS_ID+LINE from O2, i.e. Epic as deployed at KUH' c_description
139  from dual;
140create or replace view choice_unk_scheme as
141select
142  'KUH|FLO_MEAS_ID+hash:' c_key,
143  'KUH|FLO_MEAS_ID+hash' c_name,
144  'FLO_MEAS_ID+hash from O2, i.e. Epic as deployed at KUH' c_description
145  from dual;
146create or replace view kuh_flowsheet_scheme as
147select * from measure_scheme union
148select * from choice_scheme union
149select * from choice_unk_scheme;
150
151
152/* This is essentially a recursive construct, but I'm avoiding
153 * Oracle's non-standard recursive query support for some reason.
154 */
155create or replace view flowsheet_bucket_concepts as
156select depth
157     , trim(fb2.prefix) || '\' concept_path
158     , fb2.prefix name_char
159  from flowsheet_buckets fb2
160    where depth=2
161union
162select depth
163     , trim(fb3.super) || '\'
164       || trim(substr(fb3.prefix, length(fb3.super))) || '\'
165     , fb3.prefix
166  from flowsheet_buckets fb3
167    where fb3.depth=3
168union
169select fb4.depth
170     , trim(fb3.super) || '\'
171       || trim(substr(fb3.prefix, length(fb3.super))) || '\'
172       || trim(substr(fb4.prefix, length(fb4.super))) || '\'
173     , fb4.prefix
174  from flowsheet_buckets fb3
175     , flowsheet_buckets fb4
176    where fb4.depth=4
177      and fb3.depth=3
178      and fb4.super = fb3.prefix
179union
180select fb5.depth
181     , trim(fb3.super) || '\'
182       || trim(substr(fb3.prefix, length(fb3.super))) || '\'
183       || trim(substr(fb4.prefix, length(fb4.super))) || '\'
184       || trim(substr(fb5.prefix, length(fb5.super))) || '\'
185     , fb5.prefix
186  from flowsheet_buckets fb3
187     , flowsheet_buckets fb4
188     , flowsheet_buckets fb5
189    where fb5.depth=5
190      and fb4.depth=4
191      and fb3.depth=3
192      and fb4.super = fb3.prefix
193      and fb5.super = fb4.prefix;
194-- select * from flowsheet_bucket_concepts order by concept_path;
195
196
197create or replace view flowsheet_template_concepts as
198select ifd.template_id
199     , case
200         when ifd.display_name is not null then ifd.display_name
201         else ifd.template_name
202       end || ' #' || ifd.template_id name_char
203     , ifd.template_name
204     , fbc.concept_path || substr(ifd.template_name, length(fbc.name_char)+1)
205       || ' T:' || ifd.template_id || '\' concept_path
206     , fbc.depth + 1 depth
207from clarity.ip_flt_data ifd
208   , flowsheet_bucket_concepts fbc
209where replace(ifd.template_name, 'NEWBORN', 'NICU') like (fbc.name_char || '%')
210  and fbc.depth = (
211    select max(depth)
212      from flowsheet_bucket_concepts fbc2
213         where ifd.template_name like (fbc2.name_char || '%'));
214-- select * from flowsheet_template_concepts order by template_name;
215-- dups? select count(*) qty, concept_path from flowsheet_template_concepts group by concept_path order by 1 desc
216
217create or replace view flowsheet_parts as
218select ifc.flo_meas_id
219, ifc.template_id
220, ifc.line
221, ftc.concept_path
222, ftc.depth
223, ifgd.flo_row_name
224, ifgd.flo_meas_name
225, ifgd.disp_name
226, ifgd.row_typ_c
227--, ifgd.value_type_name -- value_type_name is deprecated in Clarity 2014 in favor of val_type_c
228, zcvt.name as value_type_name
229, ifgd.units as unit
230from clarity.ip_flt_comps ifc
231join flowsheet_template_concepts ftc on ifc.template_id = ftc.template_id
232join clarity.ip_flo_gp_data ifgd on ifc.flo_meas_id = ifgd.flo_meas_id
233left join clarity.zc_val_type zcvt on ifgd.val_type_c = zcvt.val_type_c;
234
235
236create or replace view flowsheet_group_concepts as
237select flo_meas_id
238, fp.concept_path || substr(fp.flo_meas_name, 1, 40)
239  || ' G:' || fp.flo_meas_id
240  || ' I#' || fp.line
241  || '\' concept_path
242, fp.depth + 1 depth
243, null concept_cd
244, to_char(fp.line, 'FM099') || '- #' || fp.flo_meas_id || ' ' || fp.disp_name || ':' name_char
245, 'FA' visualattributes
246from flowsheet_parts fp
247join clarity.zc_row_typ rt on rt.row_typ_c = fp.row_typ_c
248where rt.name <> 'Data';
249-- dups? select count(*) qty, concept_path from flowsheet_group_concepts group by concept_path order by 1 desc
250-- select count(*) from flowsheet_group_concepts;
251-- 5379 in ClarityP
252
253
254-- link the groups/flowsheets to the measures.
255create or replace view flowsheet_measure_concepts as
256select flo_meas_id
257, value_type_name
258, depth + 1 depth
259, concept_path || substr(flo_meas_name, 1, 40)
260  || ' M:' || flo_meas_id
261  -- wow... the same measure can appear in the same flowsheet/group twice
262  -- e.g. TEMP SOURCE M:7 in SAMPLE G NICU VITAL SIGNS G:202760
263  -- in NURSE FLOWSHEET T:20064.
264  -- The two TEMP SOURCEs are not independent observations.
265  -- TODO: is it OK to duplicate these flowsheet concepts?
266  || ' I#' || line
267  || '\' concept_path
268, measure_scheme.c_key || flo_meas_id concept_cd
269, to_char(line, 'FM099') || '- #' || flo_meas_id || ' ' || case
270    when disp_name is not null then disp_name
271    else flo_meas_name
272  end || case
273    when value_type_name = 'Custom List' then ' >'
274    when value_type_name = 'Blood Pressure' then ' -'
275    else ''
276  end name_char
277, case
278     when value_type_name in ('Custom List', 'Blood Pressure') then 'FA'
279     else 'LA'
280  end visualattributes
281, unit
282from (
283      -- flow measures can appear directly on a flowsheet...
284      select flo_meas_id, flo_meas_name, disp_name, value_type_name
285           , line, unit
286           , depth, concept_path
287        from flowsheet_parts
288        where flo_row_name = 'Data'
289      union
290      -- ... or in a group
291      select ifgd.flo_meas_id, ifgd.flo_meas_name, ifgd.disp_name
292           -- , ifgd.value_type_name -- value_type_name is deprecated in Clarity 2014 in favor of val_type_c
293           , zcvt.name as value_type_name
294           , line
295           , ifgd.units as unit
296           , depth, concept_path
297        from flowsheet_group_concepts fgc
298        join clarity.ip_flo_measure ifm on ifm.flo_meas_id=fgc.flo_meas_id
299        join clarity.ip_flo_gp_data ifgd on ifm.grp_flo_meas_id=ifgd.flo_meas_id
300        left join clarity.zc_val_type zcvt on ifgd.val_type_c = zcvt.val_type_c
301      ), measure_scheme;
302-- select * from flowsheet_measure_concepts order by concept_path;
303-- select * from flowsheet_measure_concepts where flo_meas_id=5;
304-- select count(*) qty, concept_path from flowsheet_group_concepts group by concept_path order by 1 desc
305-- select count(*) from flowsheet_measure_concepts;
306-- 37,476 in ClarityP
307-- select max(length(concept_path)) from flowsheet_measure_concepts;
308-- 174
309-- select count(distinct(flo_meas_id)) from flowsheet_measure_concepts;
310-- 11,063
311
312
313create or replace view flowsheet_bp_measure_concepts as
314select flo_meas_id
315, value_type_name
316, depth + 1 depth
317, concept_path || part || '\' concept_path
318, concept_cd || '_' || part concept_cd
319, '#' || flo_meas_id || ' ' || part name_char
320, 'LA' visualattributes
321, unit
322from flowsheet_measure_concepts,
323 (select 'SYSTOLIC' part from dual
324  union select 'DIASTOLIC' part from dual)
325where value_type_name = 'Blood Pressure';
326-- select * from flowsheet_bp_measure_concepts;
327
328create or replace view flowsheet_clist_concepts as
329select fmc.depth + 1 depth
330     , fmc.concept_path
331       || trim(substr(replace(fmm.choice,'\',''), 1, 20))
332       || case
333            when line is not null then ' C#' || line
334            else ' ~' || fmm.choice_hash
335          end
336       || '\' concept_path
337     , case when line is not null then to_char(line, 'FM09') || '. '
338               else '~'end  || choice name_char
339     , (case when fmm.line is not null
340        then cs.c_key || fmm.flo_meas_id || '_' || fmm.line
341        else cus.c_key || fmm.flo_meas_id || '_' || fmm.choice_hash
342        end)  CONCEPT_CD
343     , 'LA' visualattributes
344     , fmm.flo_meas_id
345     , line
346from (select distinct * from
347        -- custom values from recorded data
348        (select flo_meas_id, upper(choice) choice, choice_hash, null line
349        from flow_measure_multi fmm
350        where line is null
351        union
352        -- custom values from custom_list_match table
353        select flo_meas_id
354             , cifcl.custom_list_data
355             , null
356             , cifcl.line
357        from CLARITY.ip_flo_cust_list cifcl
358        where cifcl.custom_list_data is not null)) fmm
359  join flowsheet_measure_concepts fmc
360    on fmc.flo_meas_id = fmm.flo_meas_id
361  join choice_scheme cs on cs.c_key is not null
362  join choice_unk_scheme cus on cus.c_key is not null;
363
364select case when count(*) = 0 then 1 else 1/0 end as test_result from(
365select * from flowsheet_clist_concepts where name_char is null
366);
367
368-- select * from flowsheet_clist_concepts where name_char like '%=%';
369-- select * from flowsheet_clist_concepts where concept_path like '%GRP%';
370
371
372-- dups?
373select case when qty = 1 then 1 else 1/0 end as test_result from (
374select count(*) qty, concept_path
375from flowsheet_clist_concepts
376group by concept_path
377order by 1 desc
378) where rownum = 1;
379/* debug dups:
380select grp.qty, c.* from (
381select count(*) qty, concept_path
382from flowsheet_clist_concepts
383group by concept_path
384order by 1 desc
385) grp
386join flowsheet_clist_concepts c on grp.concept_path = c.concept_path
387where grp.qty > 1
388order by c.concept_path;
389*/
390
391
392create or replace view kuh_flowsheet_concept as
393select depth
394     , '\i2b2\Flowsheet\' || concept_path concept_path
395     , name_char || ':' name_char
396     , null concept_cd
397     , 'FA' visualattributes
398     , null C_METADATAXML
399from flowsheet_bucket_concepts
400union
401select depth, '\i2b2\Flowsheet\' || concept_path, name_char, null concept_cd, 'FA' visualattributes
402     , null
403from flowsheet_template_concepts
404union
405select depth, '\i2b2\Flowsheet\' || concept_path, name_char, concept_cd, visualattributes
406     , null
407from flowsheet_group_concepts
408union
409select depth, '\i2b2\Flowsheet\' || concept_path, name_char, concept_cd, visualattributes,
410/* Note: decision of what gets the XML for selecting numeric values is based on
411which facts are stored with the valtype_cd set to 'N'.  See epic_flowsheets_transform.sql
412*/
413case when value_type_name in
414  ('Numeric Type', 'Weight','Patient Weight', 'Blood Pressure', 'Height',
415  'Patient Height', 'Time', 'Category Type', 'Temperature') then
416'<?xml version="1.0"?>
417<ValueMetadata>
418 <Version>3.02</Version>
419 <CreationDateTime>'|| sysdate ||'</CreationDateTime>
420 <TestID>'|| concept_cd ||'</TestID>
421 <TestName>'|| name_char ||'</TestName>
422 <DataType>PosFloat</DataType>
423 <CodeType>GRP</CodeType>
424 <Loinc>Dummy2019-8@@</Loinc>
425 <Flagstouse>HL</Flagstouse>
426 <Oktousevalues>The units in this set of tests are not equivalent,
427 therefore numeric comparisons between the tests are not possible.</Oktousevalues>
428 <MaxStringLength></MaxStringLength>
429 <LowofLowValue></LowofLowValue>
430 <HighofLowValue></HighofLowValue>
431 <LowofHighValue></LowofHighValue>
432 <HighofHighValue></HighofHighValue>
433 <LowofToxicValue></LowofToxicValue>
434 <HighofToxicValue></HighofToxicValue>
435 <EnumValues></EnumValues>
436 <CommentsDeterminingExclusion>
437   <Com></Com>
438 </CommentsDeterminingExclusion>
439 <UnitValues>
440   <NormalUnits>'|| case when unit is null then 'Default' else unit end || '</NormalUnits>
441   <ConvertingUnits>
442     <Units></Units>
443     <MultiplyingFactor></MultiplyingFactor>
444   </ConvertingUnits>
445 </UnitValues>
446 <Analysis>
447   <Enums />
448   <Counts />
449   <New />
450 </Analysis>
451</ValueMetadata>' else null end C_METADATAXML
452from (select * from flowsheet_measure_concepts union select * from flowsheet_bp_measure_concepts) fmc
453union
454select depth, '\i2b2\Flowsheet\' || concept_path, name_char, concept_cd, visualattributes
455     , null
456from flowsheet_clist_concepts
457union
458select 1, '\i2b2\Flowsheet\', 'Flowsheets', null, 'FA', null from dual;
459-- eyeball: select * from kuh_flowsheet_concept order by concept_path;
460-- check: select * from kuh_flowsheet_concept where name_char is null;
461/* dups?
462select count(*), concept_path
463from kuh_flowsheet_concept
464group by concept_path
465order by 1 desc;
466*/
467
468-- select count(*) from kuh_flowsheet_concept;
469-- 345,233 in prod
Note: See TracBrowser for help on using the repository browser.