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_order_sets.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/* epic_order_sets -- harvest orderset information into the observation fact table
2
3Copyright (c) 2013 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
7Ref:
8
9  * Clarity Data Dictionary - Epic 2012
10    Catalog (Last updated: 07/14/12)
11    https://galaxy.epic.com/Search/GetFile?url=7900!44!100!1770229
12
13*/
14
15
16/*
17  Orderset usage is not stored in its own table in clarity, but rather
18  referenced by the order_proc table.  Thus to pull ordersets into the
19  observation fact table, it becomes necessary to rely on certain
20  assumptions/constraints about the relationship between ordersets and
21  procedure orders.
22 
23  Relevant Constraints:
24  (1) Ordersets have a one to many relationship with procedure/medication orders.
25  (2) Procedure/Medication orders may or may not be part of an orderset.
26  (3) Procedure/Medication orders that are a part of an orderset will target a single
27      encounter/patient/date.
28  (4) It is possible for order sets to be ordered on the same encounter/patient/
29      date.
30 
31  From Constraint (1) it is clear that selecting ordersets from the
32  procedure/medication orders tables can yield several duplicate ordersets, so they must
33  filtered in some way.
34 
35  Constraint (2) necessitates the identification of proc/med orders which are
36  ordered as a part of an orderset (cop2.order_source_c=6).
37 
38  Constraint (3) insures that there is no need to account for an orderset that
39  spans across multiple dates.
40 
41  Constraint (4) precludes us from just selecting "distinct" because an orderset
42  could be issued for a patient/encounter multiple times in the same date.
43 
44  In light of Constraint (1) and (4) we must identify the number of procedure
45  orders that are a part of each orderset to filter the duplicates.
46 
47*/
48create or replace view epic_order_sets
49as
50
51with all_proc_ordersets as(
52  select cop.pat_enc_csn_id, cop.pat_id, cop.ordering_date, cosq.orderset_id, 'P'||cop.proc_id as order_id
53  from clarity.order_proc cop
54  join clarity.order_proc_3 cop3 on cop.order_proc_id=cop3.order_id
55  join clarity.cl_osq cosq on cop3.ord_osq_id=cosq.orderset_id
56  join clarity.order_proc_2 cop2 on cop.order_proc_id = cop2.order_proc_id
57  where cop2.order_source_c=6 and cop.ordering_date is not null
58  order by cop.pat_enc_csn_id, cop.pat_id, cop.ordering_date, cosq.orderset_id
59),
60
61all_med_ordersets as(
62  select com.pat_enc_csn_id, com.pat_id, com.ordering_date, cosq.orderset_id, 'M'||com.medication_id as order_id
63  from clarity.order_med com
64  join clarity.order_med_2 com2 on com.order_med_id = com2.order_id
65  join clarity.cl_osq cosq on com2.ord_osq_id = cosq.orderset_id
66),
67
68all_ordersets as(
69  select * from all_proc_ordersets union
70  select * from all_med_ordersets
71),
72
73/*
74  gather the count of all ordersets that have the same encounter, patient
75  and order date.
76*/
77cnts_per_dt_pat_enc_ordset as(
78  select count(*) as counter, pat_enc_csn_id, pat_id, ordering_date, orderset_id, order_id
79  from all_ordersets
80  group by pat_enc_csn_id, pat_id, ordering_date, orderset_id, order_id
81  order by pat_enc_csn_id, pat_id, ordering_date, orderset_id, order_id
82),
83
84/*
85  Find the max number of the *same* procedure order for each order set.
86*/
87max_count_by_orderset as(
88  select max(counter) max_procs, pat_enc_csn_id, pat_id, ordering_date, orderset_id
89  from cnts_per_dt_pat_enc_ordset
90  group by pat_enc_csn_id, pat_id, ordering_date, orderset_id
91  order by pat_enc_csn_id, pat_id, ordering_date, orderset_id
92),
93
94/*
95  Find the largest value stored for maximum same procedure
96*/
97max_proc_over_all_ordersets as(
98  select max(max_procs) as max_procs from max_count_by_orderset
99),
100
101/*
102  Generate table of 1 to the number of maximum same procedures
103*/
104table_one_to_max_procs as(
105  select rownum as instance_num from max_proc_over_all_ordersets connect by rownum <= max_procs
106),
107
108/*
109  Generate a duplicate row for each identified same procedure (orderset issued
110  for the same patient/date multiple times in a day)
111*/
112filtered_ordersets as (
113  select pat_enc_csn_id, pat_id, ordering_date, orderset_id, instance_num
114  from max_count_by_orderset mc, table_one_to_max_procs counter
115  where mc.max_procs >= counter.instance_num
116)
117
118select to_char(pat_enc_csn_id) as encounter_ide
119  , pat_id as patient_ide
120  , 'KUH|ORDERSET_ID:'||orderset_id as concept_cd
121  , ordering_date as start_date
122  , '@' as modifier_cd
123  , instance_num as instance_num
124  , ordering_date as end_date
125  , ordering_date as update_date
126  , mod(pat_enc_csn_id, &&heron_etl_chunks)+1 as part
127  , '@' as provider_id
128  , '@' as valtype_cd
129  , '@' as tval_char
130  , to_number(null) as nval_num
131  , null as valueflag_cd
132  , null as units_cd
133  , null as location_cd
134  , to_number(null) as confidence_num
135  from filtered_ordersets
136;
137
138
139create or replace view epic_order_set_proc_and_meds
140as
141
142with all_proc_ordersets as(
143  select cop.pat_enc_csn_id, cop.pat_id, cop.ordering_date, cosq.orderset_id, 'P'||cop.proc_id as order_id, 'Procedure' as modifier
144  from clarity.order_proc cop
145  join clarity.order_proc_3 cop3 on cop.order_proc_id=cop3.order_id
146  join clarity.cl_osq cosq on cop3.ord_osq_id=cosq.orderset_id
147  join clarity.order_proc_2 cop2 on cop.order_proc_id = cop2.order_proc_id
148  where cop2.order_source_c=6 and cop.ordering_date is not null
149  order by cop.pat_enc_csn_id, cop.pat_id, cop.ordering_date, cosq.orderset_id
150),
151
152all_med_ordersets as(
153  select com.pat_enc_csn_id, com.pat_id, com.ordering_date, cosq.orderset_id, 'M'||com.medication_id as order_id, 'Medication' as modifier
154  from clarity.order_med com
155  join clarity.order_med_2 com2 on com.order_med_id = com2.order_id
156  join clarity.cl_osq cosq on com2.ord_osq_id = cosq.orderset_id
157),
158
159all_ordersets as(
160  select pat_enc_csn_id, pat_id, ordering_date, orderset_id, order_id, modifier, count(*) as instance_num
161  from(
162    select * from all_proc_ordersets union all
163    select * from all_med_ordersets
164  )
165  group by pat_enc_csn_id, pat_id, ordering_date, orderset_id, order_id, modifier
166)
167
168select to_char(pat_enc_csn_id) as encounter_ide
169  , pat_id as patient_ide
170  , 'KUH|ORDERSET_ID_M_P:'||orderset_id||'/'||order_id as concept_cd
171  , ordering_date as start_date
172  , '@' as modifier_cd
173  --TODO: Setup modifier--, 'OrderSet:'||modifier as modifier_cd
174  , instance_num as instance_num
175  , ordering_date as end_date
176  , ordering_date as update_date
177  , mod(pat_enc_csn_id, &&heron_etl_chunks)+1 as part
178  , '@' as provider_id
179  , '@' as valtype_cd
180  , '@' as tval_char
181  , to_number(null) as nval_num
182  , null as valueflag_cd
183  , null as units_cd
184  , null as location_cd
185  , to_number(null) as confidence_num
186  from all_ordersets
187;
Note: See TracBrowser for help on using the repository browser.