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/idx_dimensions_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: 6.6 KB
Line 
1/* idx_dimensions_load -- Load patient, visit dimensions from clinic billing.
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
7see also http://informatics.kumc.edu/work/wiki/ClinicIdxSource
8*/
9
10select HOSPITAL_MEDICAL_RECORD from kupi.idx_table where 1 = 0;
11
12select MRN from idx_patient_dimension where 1 = 0;
13
14create or replace view idx_audit_info as
15select source_cd, description
16  from blueherondata.source_master@deid
17    where source_cd like 'IDX%';
18
19
20/* TODO: handle unrecognized MRNs. For now, just ensure they're in the noise.
21
22This currently fails due to 202 records:
23-- 126,506 matches
24--     202 mismatches
25
26Pending discussion with EMPI staff (see ticket #46), we'll live with
27some noise here.
28
29Beware; these things are not numbers, and Epic has dirty data in the MRN column:
30select to_number('033121O') from dual;
31
32We've trimmed leading zeros from the IDX data (see #2451) so make sure to also
33trim zeros from other sources when joining.
34*/
35
36whenever sqlerror continue;
37alter table nightherondata.provider_dimension add (npi integer);
38whenever sqlerror exit;
39
40-- truncate table nightherondata.provider_dimension;
41insert into nightherondata.provider_dimension (provider_id, provider_path, name_char, npi)
42select provider_id, '/code_' || provider_id provider_path, name_char, npi
43from provider_dimension_idx
44;
45
46WHENEVER SQLERROR CONTINUE;
47drop table mismatched_mrns;
48WHENEVER SQLERROR EXIT;
49
50create table mismatched_mrns as
51select pd.mrn,
52(select count(distinct MRN) from idx_patient_dimension) ct
53from idx_patient_dimension pd
54  left join mrn_patient_mappings pmap
55    on pd.MRN = pmap.MRN
56where pmap.MRN is null
57;
58
59select case
60  when (mismatches / patient_qty) < .01 then 1
61  else 1/0
62  end as test_result from (
63select mismatches
64     , (select count(distinct MRN) from idx_patient_dimension) as patient_qty
65from (
66select count(*) as mismatches from (
67select pd.mrn
68from idx_patient_dimension pd
69  left join mrn_patient_mappings pmap
70    on pd.MRN = pmap.MRN
71where pmap.MRN is null
72 )
73)
74);
75
76/** encounter_mapping
77
78delete from NightHeronData.encounter_mapping
79where encounter_ide_source =
80 (select source_cd from idx_audit_info);
81*/
82
83insert into NightHeronData.encounter_mapping
84  (encounter_num, encounter_ide,
85   encounter_ide_status, encounter_ide_source, project_id,
86   patient_ide, patient_ide_source,
87   import_date, upload_id, download_date, sourcesystem_cd )
88select
89   coalesce(vd_agg.encounter_num,
90            NightHeronData.SQ_UP_ENCDIM_ENCOUNTERNUM.nextval) as encounter_num
91   , vd_agg.encounter_ide, 'A' as encounter_ide_status
92   , aud.source_cd as encounter_ide_source
93   , '@' as project_id
94   , vd_agg.patient_ide as patient_ide
95   , vd_agg.patient_ide_source
96   , sysdate as import_date
97   , up.upload_id
98   , :download_date as download_date
99   , aud.source_cd as sourcesystem_cd
100   from
101   (select min(emap_pday.encounter_num) encounter_num, max(vd.part) part,
102           vd.encounter_ide, vd.patient_ide, pmap.patient_ide_source
103    from idx_visit_dimension vd
104    join mrn_patient_mappings pmap
105      on pmap.MRN = vd.patient_ide
106    join nightherondata.patient_mapping epic_ids
107      on pmap.patient_num = epic_ids.patient_num
108     and epic_ids.patient_ide_source = (select source_cd from epic_audit_info)
109    left join (
110      --Assumes implicit structure of encounter_ide's for pat_day_visit
111      --e.g. "YYYYMMDD" + [pat_id] (see: epic_dimensions_load)
112      select to_date(substr(encounter_ide,1,8),'yyyymmdd') as encounter_date,
113             encounter_num, patient_ide
114      from NightHeronData.encounter_mapping
115      where encounter_ide_source = (select source_cd
116                                    from epic_pat_day_audit_info)
117      ) emap_pday
118      on emap_pday.encounter_date = vd.start_date
119     and emap_pday.patient_ide = epic_ids.patient_ide
120    group by vd.encounter_ide, vd.patient_ide, pmap.patient_ide_source) vd_agg
121   join NightHeronData.upload_status up on up.upload_id = :upload_id
122 , idx_audit_info aud
123   where vd_agg.part = :part
124;
125
126
127/* encounter_mappings from IDX */
128create or replace view idx_encounter_mapping as
129select emap.encounter_ide, emap.encounter_num, emap.encounter_ide_source
130     , emap.patient_ide as MRN
131from NightHeronData.encounter_mapping emap
132  join idx_audit_info aud
133    on emap.encounter_ide_source = aud.source_cd
134   and emap.encounter_ide_status = 'A';
135
136/* Did we find pretty much all of the visits to map ? */
137select case
138  when encounters_mapped / total_kupi_encounters > 0.99 then 1
139  else 1/0
140  end as test_result
141from (
142select encounters_mapped
143     , (select count(distinct BILL_INV_NUMBER)
144        from kupi.idx_table) as total_kupi_encounters
145from (
146select count(*) as encounters_mapped
147from idx_encounter_mapping emap
148)
149);
150
151
152/* Subset of idx_encounter_mapping which include only the idx encounters
153   that are not also mapped to another source */
154create or replace view only_new_idx_encounter_mapping as
155with not_idx_enc_map as
156(select emap.*
157 from NightHeronData.encounter_mapping emap
158 join idx_audit_info aud
159   on emap.encounter_ide_source != aud.source_cd
160  and emap.encounter_ide_status = 'A'
161)
162select idx_enc_map.*
163from idx_encounter_mapping idx_enc_map
164left join not_idx_enc_map
165       on idx_enc_map.encounter_num = not_idx_enc_map.encounter_num
166where not_idx_enc_map.encounter_ide is null;
167
168insert into NightHerondata.visit_dimension (
169  ENCOUNTER_NUM, PATIENT_NUM,
170  active_status_cd, start_date,
171  import_date, upload_id, download_date, sourcesystem_cd )
172select
173    emap.ENCOUNTER_NUM
174  , pmap.PATIENT_NUM
175  , 'A'
176  , vd.start_date
177  , sysdate as import_date
178  , up.upload_id
179  , up.load_date
180  , up.source_cd
181from idx_visit_dimension vd
182join mrn_patient_mappings pmap
183  on pmap.MRN = vd.patient_ide
184join only_new_idx_encounter_mapping emap
185  on vd.encounter_ide = emap.encounter_ide
186   , NightHeronData.upload_status up
187where up.upload_id = :upload_id
188  and vd.part = :part
189;
190
191/* Clean up after failed bsr_clinical_facts_load.sql
192delete
193-- select *
194from nightherondata.visit_dimension vd
195where vd.sourcesystem_cd = 'BSR@kumc.edu';
196
197delete
198-- select *
199from nightherondata.encounter_mapping em
200where em.sourcesystem_cd = 'BSR@kumc.edu';
201*/
202
203
204update NightHeronData.upload_status
205  set loaded_record = (select (
206    select count(*)
207    from NightHeronData.encounter_mapping
208    where encounter_ide_source = (
209       select source_cd from idx_audit_info)
210       ) + (
211    select count(*)
212    from NightHeronData.visit_dimension
213    where sourcesystem_cd = (
214       select source_cd from idx_audit_info)
215       ) from dual)
216  where upload_id = :upload_id;
217update NightHeronData.upload_status
218  set no_of_record = loaded_record
219  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.