source: heron_load/idx_clinical_facts_load.sql

heron-michigan
Last change on this file was 0:42ad7288920a, checked in by Matt Hoag <mhoag@…>, 2 years ago

Merge with demo_concepts_3800

File size: 6.1 KB
Line 
1/* idx_clinical_facts_load.sql -- load IDX clinical facts
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
9see also http://informatics.kumc.edu/work/wiki/HeronLoad ,
10         http://informatics.kumc.edu/work/wiki/ClinicIdxSource
11
12to start over, just:
13truncate table observation_fact_upload;
14
15See also: i2b2_facts_deid.sql for where the data goes next.
16*/
17alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' ;
18
19
20/* Check IDX views. */
21select HOSPITAL_MEDICAL_RECORD from "&&kupi".idx_table where 1 = 0;
22select concept_cd from observation_fact_procedure where 1 = 0;
23
24
25truncate table observation_fact_upload;
26whenever sqlerror continue;
27alter table observation_fact_upload
28  disable constraint observation_fact_pk;
29whenever sqlerror exit;
30
31/** Diagnosis facts
32 */
33insert into observation_fact_upload (
34      concept_cd, valtype_cd, tval_char,
35      patient_num, encounter_num, sub_encounter,
36      start_date,
37      end_date,
38      provider_id,
39      modifier_cd,
40      instance_num,
41      import_date, upload_id, download_date, sourcesystem_cd)
42select
43      f.concept_cd, f.valtype_cd, f.tval_char,
44      pmap.patient_num, emap.encounter_num, f.encounter_ide,
45      f.start_date,
46      f.end_date,
47      f.provider_id,
48      f.modifier_cd,
49      f.instance_num,
50      sysdate as import_date, up.upload_id, :download_date, up.source_cd
51from observation_fact_dx f
52  join idx_encounter_mapping emap
53    on f.encounter_ide = emap.encounter_ide
54  join mrn_patient_mappings pmap
55    on f.patient_ide = pmap.MRN
56     , NightHeronData.upload_status up
57where up.upload_id = :upload_id
58  and f.part = :part;
59
60
61/** Procedure facts
62 */
63insert into observation_fact_upload (
64      concept_cd, valtype_cd, tval_char,
65      patient_num, encounter_num, sub_encounter,
66      start_date,
67      end_date,
68      provider_id,
69      modifier_cd,
70      instance_num,
71      import_date, upload_id, download_date, sourcesystem_cd)
72select
73      f.concept_cd, f.valtype_cd, f.tval_char,
74      pmap.patient_num, emap.encounter_num, f.encounter_ide,
75      f.start_date,
76      f.end_date,
77      f.provider_id,
78      f.modifier_cd,
79      f.instance_num,
80      sysdate as import_date, up.upload_id, :download_date, up.source_cd
81from observation_fact_procedure f
82  join idx_encounter_mapping emap
83    on f.encounter_ide = emap.encounter_ide
84    join mrn_patient_mappings pmap
85    on f.patient_ide = pmap.MRN
86     , NightHeronData.upload_status up
87where up.upload_id = :upload_id
88  and f.part = :part;
89
90
91/** Area facts (Areas w/in Clinical Service Lines)
92 */
93insert into observation_fact_upload (
94      concept_cd, valtype_cd, tval_char,
95      patient_num, encounter_num, sub_encounter,
96      start_date,
97      end_date,
98      provider_id,
99      modifier_cd,
100      instance_num,
101      import_date, upload_id, download_date, sourcesystem_cd)
102select
103      f.concept_cd, f.valtype_cd, f.tval_char,
104      pmap.patient_num, emap.encounter_num, f.encounter_ide,
105      f.start_date,
106      f.end_date,
107      f.provider_id,
108      f.modifier_cd,
109      f.instance_num,
110      sysdate as import_date, up.upload_id, :download_date, up.source_cd
111from observation_fact_area f
112  join idx_encounter_mapping emap
113    on f.encounter_ide = emap.encounter_ide
114    join mrn_patient_mappings pmap
115    on f.patient_ide = pmap.MRN
116     , NightHeronData.upload_status up
117where up.upload_id = :upload_id
118  and f.part = :part;
119 
120
121/** Place of Service
122 */
123insert into observation_fact_upload (
124      concept_cd, valtype_cd, tval_char,
125      patient_num, encounter_num, sub_encounter,
126      start_date,
127      end_date,
128      provider_id,
129      modifier_cd,
130      instance_num,
131      import_date, upload_id, download_date, sourcesystem_cd)
132select
133      f.concept_cd, f.valtype_cd, f.tval_char,
134      pmap.patient_num, emap.encounter_num, f.encounter_ide,
135      f.start_date,
136      f.end_date,
137      f.provider_id,
138      f.modifier_cd,
139      f.instance_num,
140      sysdate as import_date, up.upload_id, :download_date, up.source_cd
141from observation_fact_pos f
142  join idx_encounter_mapping emap
143    on f.encounter_ide = emap.encounter_ide
144    join mrn_patient_mappings pmap
145    on f.patient_ide = pmap.MRN
146     , NightHeronData.upload_status up
147where up.upload_id = :upload_id
148  and f.part = :part;
149
150
151/** Billing, service provider
152 */
153insert into observation_fact_upload (
154      concept_cd, valtype_cd, tval_char,
155      patient_num, encounter_num, sub_encounter,
156      start_date,
157      end_date,
158      provider_id,
159      modifier_cd,
160      instance_num,
161      import_date, upload_id, download_date, sourcesystem_cd)
162select
163      f.concept_cd, f.valtype_cd, f.tval_char,
164      pmap.patient_num, emap.encounter_num, f.encounter_ide,
165      f.start_date,
166      f.end_date,
167      f.provider_id,
168      f.modifier_cd,
169      f.instance_num,
170      sysdate as import_date, up.upload_id, :download_date, up.source_cd
171from observation_fact_idx_provider f
172  join idx_encounter_mapping emap
173    on f.encounter_ide = emap.encounter_ide
174    join mrn_patient_mappings pmap
175    on f.patient_ide = pmap.MRN
176     , NightHeronData.upload_status up
177where up.upload_id = :upload_id
178  and f.part = :part;
179
180
181/* debugging:
182
183drop index observation_fact_debug;
184
185create index observation_fact_debug
186 on observation_fact_upload
187 (ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM)
188  nologging;
189
190select count(*), ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM
191from observation_fact_upload
192group by ENCOUNTER_NUM, CONCEPT_CD, PROVIDER_ID, START_DATE, MODIFIER_CD, INSTANCE_NUM
193having count(*) > 1
194;
195*/
196
197
198/* For this upload of data, check primary key constraints. */
199alter table observation_fact_upload
200  enable constraint observation_fact_pk
201  /* TODO: log errors ... ? #2117 */
202  ;
203
204
205/* Summary stats. */
206update NightHeronData.upload_status
207  set loaded_record = (select count(*) from observation_fact_upload)
208    , no_of_record = (select count(*) from observation_fact_dx) +
209                     (select count(*) from observation_fact_procedure) +
210                     (select count(*) from observation_fact_area)
211  where upload_id = :upload_id;
Note: See TracBrowser for help on using the repository browser.