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/uhc_clinical_facts_load.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: 12.1 KB
Line 
1/* uhc_clinical_facts_load.sql -- load UHC data into identified HERON repository
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
7patterned after epic_facts_load.sql
8
9Information on how UHC data is organized came from documents downloaded from
10https://www.uhc.edu/.  Specifically, "CDBRM_Downloads_File_Specification.xls"
11which was downloaded and provided as an ETL reference by by the KU Hospital
12Organizational Improvement team.
13*/
14select patientid from uhc.encounter where 1=0;
15
16select pat_id from CLARITY.patient where 1 = 0;
17
18/* Verify all patient IDs are in patient mapping table
19select count(*)
20from NightHeronData.patient_mapping pmap
21where pmap.patient_ide_source = 'SMS@kumed.com'
22and pmap.patient_ide_status = 'A';
23join patient_dimension enc
24on en.patid = pmap.patient_ide  as matched_qty
25
26select count(distinct patid) from patient_dimension) as patient_qty */
27
28
29create or replace view uhc_audit_info as
30select * from BlueHeronData.source_master@DEID
31where source_cd like 'UHC@%';
32 
33insert into nightherondata.source_master
34  select source_cd, description, sysdate
35    from uhc_audit_info
36    where source_cd not in
37      (select source_cd from nightherondata.source_master);
38     
39
40whenever sqlerror continue;
41-- !uhc_encounter_mapping was originally a view, clean it up.
42drop view uhc_encounter_mapping;
43truncate table uhc_encounter_mapping;
44drop table uhc_encounter_mapping;
45whenever sqlerror exit;
46
47/* Map UHC encounters via HSP_ACCOUNT_ID, and failing that, MPI_ID.
48
49When the KU hospital switched over to fully utilize EPIC, it no longer used SMS
50for to generate the encounter numbers for UHC. The encounterid used in UHC now uses
51the column hsp_account_id from the pat_enc_hsp table. Before Sept 2013 it was using the
52column mpi_encounter_id from the hsp_acct_mpi table.
53
54Note an HSP_ACCOUNT_ID with a corresponding MPI_ID is already mapped to the
55same encounter_num.
56*/
57create table uhc_encounter_mapping as
58with hsp_map as (
59select * from NightHeronData.encounter_mapping
60where ENCOUNTER_IDE_SOURCE = (select source_cd from epic_hsp_audit_info)
61),
62mpi_map as (
63select * from NightHeronData.encounter_mapping
64where ENCOUNTER_IDE_SOURCE = (select source_cd from sms_audit_info)
65)       
66select enc.encounterid as uhcEncounterID,
67       coalesce(hsp_map.encounter_num, mpi_map.encounter_num) encounter_num,
68       coalesce(hsp_map.encounter_ide_source, mpi_map.encounter_ide_source) encounter_ide_source
69from uhc.encounter enc
70left join hsp_map on enc.encounterid = hsp_map.encounter_ide
71left join mpi_map on enc.encounterid = mpi_map.encounter_ide
72where coalesce(hsp_map.encounter_num, mpi_map.encounter_num) is not null
73;
74create unique index uhc_encounter_mapping_pk
75 on uhc_encounter_mapping
76 (uhcEncounterID)
77  nologging;
78
79
80
81/* Ensure that most of the uhc encounter_ids are mapped to encounter_nums
82 * If not a number of uhc facts may not be included.
83 * TODO instead of a fall over test maybe implement log and trundle on test...
84 */
85 --Eyeball coverage.  Test data needs to be changed before this can be a fall over test
86/*
87select case when percent_mapped < 99 then 1/0 else percent_mapped end percent_mapped,
88       num_of_unmapped,
89       total_uhc_enc
90from
91(select round(100 * ((uhc_encounters - unmapped_encounters) / uhc_encounters), 5) percent_mapped,
92        unmapped_encounters num_of_unmapped,
93        uhc_encounters total_uhc_enc
94 from
95 (select (select count(*) from uhc_visit_dimension) as uhc_encounters,
96         (select count(*) from
97            (select distinct uhc.encounterid from uhc.encounter uhc
98             minus
99             select uhcEncounterID from uhc_encounter_mapping)
100            ) as unmapped_encounters
101  from dual)
102);
103*/
104
105truncate table observation_fact_upload;
106whenever sqlerror continue;
107alter table observation_fact_upload
108  disable constraint observation_fact_pk;
109whenever sqlerror exit;
110
111/***********************
112  UHC base observation load
113 ***********************/
114insert into observation_fact_upload (
115      concept_cd, valtype_cd, tval_char,
116      patient_num, encounter_num, sub_encounter,
117      start_date,
118      end_date,
119      provider_id,
120      modifier_cd,
121      instance_num,
122      nval_num,
123      update_date,
124      import_date, upload_id, download_date, sourcesystem_cd)
125select
126      f.concept_cd, f.valtype_cd, f.tval_char,
127      pmap.patient_num, emap.encounter_num, f.encounterid,
128      f.start_date,
129      f.end_date,
130      f.provider_id,
131      f.modifier_cd,
132      f.instance_num,
133      f.nval_num,
134      f.update_date,
135      sysdate as import_date, up.upload_id, :download_date, up.source_cd
136from observation_fact_uhc f
137join uhc_encounter_mapping emap
138   on f.encounterid = emap.uhcEncounterID
139join NIGHTHERONDATA.patient_mapping pmap
140    -- patientid's from UHC can have leading '0's, the pmap does not
141    on ltrim(f.patientid, '0') = pmap.patient_ide
142 and pmap.patient_ide_source ='SMS@kumed.com'
143 and pmap.patient_ide_status = 'A'
144     , NightHeronData.upload_status up
145where up.upload_id = :upload_id
146and f.part = :part
147;
148
149/***********************
150  UHC dx observation load
151 ***********************/
152insert into observation_fact_upload (
153      concept_cd, valtype_cd, tval_char,
154      patient_num, encounter_num, sub_encounter,
155      start_date,
156      end_date,
157      provider_id,
158      modifier_cd,
159      instance_num,
160      nval_num,
161      update_date,
162      import_date, upload_id, download_date, sourcesystem_cd)
163select
164      f.concept_cd, f.valtype_cd, f.tval_char,
165      pmap.patient_num, emap.encounter_num, f.encounterid,
166      f.start_date,
167      f.end_date,
168      f.provider_id,
169      f.modifier_cd,
170      f.instance_num,
171      f.nval_num,
172      f.update_date,
173      sysdate as import_date, up.upload_id, :download_date, up.source_cd
174from  uhc_observation_fact_dx f
175join uhc_encounter_mapping emap
176   on f.encounterid = emap.uhcEncounterID
177join NIGHTHERONDATA.patient_mapping pmap
178    -- patientid's from UHC can have leading '0's, the pmap does not
179    on ltrim(f.patientid, '0') = pmap.patient_ide
180   and pmap.patient_ide_source = 'SMS@kumed.com'
181    and pmap.patient_ide_status = 'A'
182    , NightHeronData.upload_status up
183where up.upload_id = :upload_id
184and f.part = :part
185;
186
187/***********************
188  UHC procedure observation load
189 ***********************/
190insert into observation_fact_upload (
191      concept_cd, valtype_cd, tval_char,
192      patient_num, encounter_num, sub_encounter,
193      start_date,
194      end_date,
195      provider_id,
196      modifier_cd,
197      instance_num,
198      nval_num,
199      update_date,
200      import_date, upload_id, download_date, sourcesystem_cd)
201select
202      f.concept_cd, f.valtype_cd, f.tval_char,
203      pmap.patient_num, emap.encounter_num, f.encounterid,
204      f.start_date,
205      f.end_date,
206      f.provider_id,
207      f.modifier_cd,
208      f.instance_num,
209      f.nval_num,
210      f.update_date,
211      sysdate as import_date, up.upload_id, :download_date, up.source_cd
212from  uhc_observation_fact_proc f
213join uhc_encounter_mapping emap
214   on f.encounterid = emap.uhcEncounterID
215  join NIGHTHERONDATA.patient_mapping pmap
216    -- patientid's from UHC can have leading '0's, the pmap does not
217    on ltrim(f.patientid, '0') = pmap.patient_ide
218 and pmap.patient_ide_source ='SMS@kumed.com'
219 and pmap.patient_ide_status = 'A'
220 , NightHeronData.upload_status up
221where up.upload_id = :upload_id
222and f.part = :part
223;
224
225/***********************
226  UHC core measure observation load
227 ***********************/
228insert into observation_fact_upload (
229      concept_cd, valtype_cd, tval_char,
230      patient_num, encounter_num, sub_encounter,
231      start_date,
232      end_date,
233      provider_id,
234      modifier_cd,
235      instance_num,
236      nval_num,
237      update_date,
238      import_date, upload_id, download_date, sourcesystem_cd)
239select
240      f.concept_cd, f.valtype_cd, f.tval_char,
241      pmap.patient_num, emap.encounter_num, f.encounterid,
242      f.start_date,
243      f.end_date,
244      f.provider_id,
245      f.modifier_cd,
246      f.instance_num,
247      f.nval_num,
248      f.update_date,
249      sysdate as import_date, up.upload_id, :download_date, up.source_cd
250from  uhc_observation_fact_core f
251join uhc_encounter_mapping emap
252   on f.encounterid = emap.uhcEncounterID
253  join NIGHTHERONDATA.patient_mapping pmap
254    -- patientid's from UHC can have leading '0's, the pmap does not
255    on ltrim(f.patientid, '0') = pmap.patient_ide
256 and pmap.patient_ide_source ='SMS@kumed.com'
257 and pmap.patient_ide_status = 'A'
258 , NightHeronData.upload_status up
259where up.upload_id = :upload_id
260and f.part = :part
261;
262
263/***********************
264  UHC Expected LOS observation load
265 ***********************/
266insert into observation_fact_upload (
267      concept_cd, valtype_cd, tval_char,
268      patient_num, encounter_num, sub_encounter,
269      start_date,
270      end_date,
271      provider_id,
272      modifier_cd,
273      instance_num,
274      nval_num,
275      update_date,
276      import_date, upload_id, download_date, sourcesystem_cd)
277select
278      f.concept_cd, f.valtype_cd, f.tval_char,
279      pmap.patient_num, emap.encounter_num, f.encounterid,
280      f.start_date,
281      f.end_date,
282      f.provider_id,
283      f.modifier_cd,
284      f.instance_num,
285      f.nval_num,
286      f.update_date,
287      sysdate as import_date, up.upload_id, :download_date, up.source_cd
288from  uhc_observation_fact_expected f
289join uhc_encounter_mapping emap
290   on f.encounterid = emap.uhcEncounterID
291  join NIGHTHERONDATA.patient_mapping pmap
292    -- patientid's from UHC can have leading '0's, the pmap does not
293    on ltrim(f.patientid, '0') = pmap.patient_ide
294 and pmap.patient_ide_source ='SMS@kumed.com'
295 and pmap.patient_ide_status = 'A'
296 , NightHeronData.upload_status up
297where up.upload_id = :upload_id
298and f.part = :part
299;
300
301/***********************
302  UHC physician Specialty and role observation load
303 ***********************/
304insert into observation_fact_upload (
305      concept_cd, valtype_cd, tval_char,
306      patient_num, encounter_num, sub_encounter,
307      start_date,
308      end_date,
309      provider_id,
310      modifier_cd,
311      instance_num,
312      nval_num,
313      update_date,
314      import_date, upload_id, download_date, sourcesystem_cd)
315select
316      f.concept_cd, f.valtype_cd, f.tval_char,
317      pmap.patient_num, emap.encounter_num, f.encounterid,
318      f.start_date,
319      f.end_date,
320      f.provider_id,
321      f.modifier_cd,
322      f.instance_num,
323      f.nval_num,
324      f.update_date,
325      sysdate as import_date, up.upload_id, :download_date, up.source_cd
326from  uhc_observation_fact_spec f
327join uhc_encounter_mapping emap
328   on f.encounterid = emap.uhcEncounterID
329  join NIGHTHERONDATA.patient_mapping pmap
330    -- patientid's from UHC can have leading '0's, the pmap does not
331    on ltrim(f.patientid, '0') = pmap.patient_ide
332 and pmap.patient_ide_source ='SMS@kumed.com'
333 and pmap.patient_ide_status = 'A'
334 , NightHeronData.upload_status up
335where up.upload_id = :upload_id
336and f.part = :part
337;
338
339
340insert into etl_test_values (test_value, test_domain, test_name, result_id
341                           , result_date)
342with test_key as (
343  select 'University HealthSystem Consortium' test_domain,
344  'all_uhc_pat_uploaded' test_name from dual
345  )
346,
347test_values as (
348  select round(uploaded_pats * 100 / uhc_pats, 2) test_value
349  from
350  (select
351      (select count (distinct patient_num)
352       from observation_fact_upload) uploaded_pats
353    , (select greatest(count (distinct patientid), 1) from uhc.encounter) uhc_pats
354   from dual)
355)
356select tv.test_value, tk.test_domain, tk.test_name
357     , sq_result_id.nextval, sysdate
358from test_values tv, test_key tk;
359
360
361/* For this upload of data, check primary key constraints. */
362alter table observation_fact_upload
363  enable constraint observation_fact_pk
364  /* TODO: log errors ... ? #2117 */
365  ;
366
367/* Summary stats */
368update NightHeronData.upload_status
369  set loaded_record = (select count(*) from observation_fact_upload)
370    , no_of_record =
371        (select count(*) from observation_fact_uhc) +
372        (select count(*) from uhc_observation_fact_dx) +
373        (select count(*) from uhc_observation_fact_proc) +
374        (select count(*) from uhc_observation_fact_core) +
375        (select count(*) from uhc_observation_fact_expected) +
376        (select count(*) from uhc_observation_fact_spec)
377  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.