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/patient_nums/update_patient_set_numbers.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.0 KB
Line 
1/**************
2 * Script 1: ID
3 **************
4 * Constructed from one-time-update-ofquery-patient-nums.sql
5 * on branch backup_pat_enc_699
6 *
7 * Script 1 does nto have any user visible database modifications.
8 */
9
10--Connected to Night heron
11select 1 as connected from nightherondata.encounter_mapping where 0 = 1;
12
13--Can access blueherondata queries
14select result_instance_id from blueherondata.qt_query_result_instance@deid where 0 = 1;
15
16--Can access backup mappings
17select 1 as connected from nheron_backup.backup_info@kumc where 0 = 1;
18
19/*********************************
20  Create a view containing the release entry from 3 months ago
21 *********************************/
22create or replace view release_date_of_previous as
23select *
24from (select q.*, rownum rnum
25      from (select * from nheron_backup.backup_info@kumc order by release_date desc) q)
26where rnum  = 4; -- 3 months ago
27
28/*eyeball it
29select * from release_date_of_previous;
30*/
31
32/**********************************
33 Create view containing only the last release entry from backup_info
34 **********************************/
35create or replace view current_release as
36select *
37from (select q.*, rownum rnum
38      from (select * from nheron_backup.backup_info@kumc order by release_date desc) q)
39where rnum = 1;
40
41/*eyeball it
42select * from current_release;
43*/
44
45
46/***********************************
47 Create a view of selected query results (within the last "n" releases but not the current release)
48 and append the backup_id for patient mapping purposes
49 ***********************************/
50create or replace view query_to_backup_id as
51select *
52from(
53select qr.result_instance_id
54     , qr.query_instance_id
55     , qr.start_date
56     , bi.backup_id
57     , bi.backup_description
58     , bi.release_date
59       --! column identifying the most recent associated release_date
60     , max(bi.release_date) keep (dense_rank last order by bi.release_date)
61                           over (partition by qr.result_instance_id) max_release_date
62from (select result_instance_id, query_instance_id, start_date
63      from blueherondata.qt_query_result_instance@deid) qr
64                                       --! let the max backup be the release before the current one
65join nheron_backup.backup_info@kumc bi on bi.release_date < (select release_date from current_release)
66                                       --! KEYWORD:REVERSE
67                                       --! ON clause to be done during a reversal
68                                       --! see script 4 "update_patient_numbers_in_patient_set_collection.sql
69                                       --! for further instructions.
70                                       --!#################################
71                                       --on qr.start_date > bi.release_date
72                                       --!#################################
73                                       --! get all queries that start after the beginning of the release date window
74                                       and qr.start_date > (select release_date from release_date_of_previous)
75                                       --! ignore queries that are part of the current release
76                                       and qr.start_date < (select release_date from current_release))
77where release_date = max_release_date
78;
79
80/*eyeball it
81select * from query_to_backup_id;
82select backup_description, count(*) from query_to_backup_id group by backup_description;
83select backup_description, count(distinct result_instance_id)
84  from query_to_backup_id group by backup_description;
85*/
86
87/***********************************
88 Create a view associating all patient sets with backup_id for patient mapping purposes
89 ***********************************/
90create or replace view patientset_to_backup_id as
91select pscol.*, qtb.backup_id, qtb.query_instance_id
92from blueherondata.qt_patient_set_collection@deid pscol
93join query_to_backup_id qtb on pscol.result_instance_id = qtb.result_instance_id
94;
95
96/*eyeball it
97select * from patientset_to_backup_id;
98select backup_id, count(*) from patientset_to_backup_id group by backup_id;
9913 seconds
100*/
101
102whenever sqlerror continue;
103truncate table patientset_with_backup_id;
104drop table patientset_with_backup_id;
105whenever sqlerror exit;
106
107/***********************************
108 Create a temporary table of patient sets to backup_id
109 ***********************************/
110create global temporary table patientset_with_backup_id on commit preserve rows as
111select * from patientset_to_backup_id;
112
113/*eyeball it
114select * from patientset_with_backup_id;
115select backup_id, count(*) from patientset_with_backup_id group by backup_id;
116select backup_id, count(distinct result_instance_id) from patientset_with_backup_id group by backup_id;
117*/
118
119whenever sqlerror continue;
120truncate table patient_mapping_backup;
121drop table patient_mapping_backup;
122whenever sqlerror exit;
123
124/***********************************
125 Create a temporary table of the backup patient mappings including only those
126 that we need (has a backup_id required by a patient set)
127 ***********************************/
128create table patient_mapping_backup as
129
130with used_backup_ids as
131(select distinct backup_id from patientset_with_backup_id)
132/*eyeball it
133select distinct backup_id from patientset_with_backup_id;
134*/
135
136select *
137from nheron_backup.patient_mapping_backup@kumc
138where backup_id in (select backup_id from used_backup_ids)
139;
140-- 218 seconds
141
142create index patient_mapping_backup_source on patient_mapping_backup (patient_num, backup_id);
143
144/*eyeball it
145select count(*) from patient_mapping_backup;
146select distinct (patient_ide_source) from patient_mapping_backup;
147select distinct (backup_id) from patient_mapping_backup;
148
149select count(distinct patient_ide), backup_id, patient_ide_source
150from patient_mapping_backup group by backup_id, patient_ide_source;
151*/
152
153whenever sqlerror continue;
154truncate table old_patient_to_new;
155drop table old_patient_to_new;
156whenever sqlerror exit;
157
158/***********************************
159 Generate a mapping old patient num to new patient number.
160 ***********************************/
161create table old_patient_to_new as
162(select distinct pswb.*, pmap.patient_num current_patient_num
163from patientset_with_backup_id pswb
164left join patient_mapping_backup pmapb on pswb.patient_num = pmapb.patient_num
165                                 and pswb.backup_id = pmapb.backup_id
166left join nightherondata.patient_mapping pmap on pmapb.patient_ide = pmap.patient_ide
167                                        and pmapb.patient_ide_source = pmap.patient_ide_source)
168;
169
170/*eyeball it
171select count(*) from old_patient_to_new;
172select * from old_patient_to_new;
173select count(distinct patient_set_coll_id) from old_patient_to_new;
174
175!Instances where there are patient sets but no mapping... (always will be because of left join ---> null)
176select patient_set_coll_id, patient_num from patientset_with_backup_id minus
177select patient_set_coll_id, patient_num from old_patient_to_new order by patient_num
178!402 (was) ----> 0 (now)  it should be 0.
179
180!Instances where the patient mapping has two or more mappings of PATIENT_NUM -> CURRENT_PATIENT_NUM
181!This identifies the need to prefer a mapping to something other than null. (reason for old_patient_to_new_filtered)
182select * from old_patient_to_new
183where patient_set_coll_id in (select patient_set_coll_id
184                              from (select count(*), patient_set_coll_id
185                                    from old_patient_to_new
186                                    group by patient_set_coll_id
187                                    having count(*)>1)) order by patient_set_coll_id, current_patient_num;
188
189
190!Medicine lodge had this problem:
191 col id   resul idx   pnum  bi  qinst new pnum
192!26429598       22351   2896    2941    41      18453   59798
193!26429598       22351   2896    2941    41      18453   198509
194
195!Verify that no PATIENT_SET_COLL_ID has more than on real CURRENT_PATIENT_NUM
196select patient_set_coll_id from old_patient_to_new
197where patient_set_coll_id in (select patient_set_coll_id
198                              from (select count(*), patient_set_coll_id
199                                    from old_patient_to_new
200                                    group by patient_set_coll_id
201                                    having count(*)>1))
202and current_patient_num is not null
203group by patient_set_coll_id
204having count(*) > 1;
205
206!How many mappings have PATIENT_NUM -> NULL
207select count(*) from old_patient_to_new where current_patient_num is null;
208!410
209*/
210
211
212/***********************************
213 Filter mapping of old patient num to new patient number such that PATIENT_NUM -> CURRENT_PATIENT_NUM
214 exist per PATIENT_SET_COLL_ID.  The filtered ones should all be null.
215 ***********************************/
216whenever sqlerror continue;
217truncate table old_patient_to_new_filtered;
218drop table old_patient_to_new_filtered;
219whenever sqlerror exit;
220
221create global temporary table old_patient_to_new_filtered on commit preserve rows as
222
223select *
224from (select optn.*
225          , max(cpnum_mod) keep (dense_rank last order by cpnum_mod)
226                           over (partition by patient_set_coll_id) max_current_patient_num
227      from (select inner.*
228                 , (case when current_patient_num is null then 0 else current_patient_num end) as cpnum_mod
229            from old_patient_to_new inner) optn)
230where cpnum_mod = max_current_patient_num
231;
232
233/*eyeball it
234select count(*) from old_patient_to_new_filtered;
235
236!Should be 0; don't want any duplicate mappings for a patient_set_coll_id
237select * from old_patient_to_new
238where patient_set_coll_id in (select patient_set_coll_id
239                              from (select count(*), patient_set_coll_id
240                                    from old_patient_to_new_filtered
241                                    group by patient_set_coll_id
242                                    having count(*)>1)) order by patient_set_coll_id, current_patient_num;
243
244!List of mappings where fore each entry there is no CURRENT_PATIENT_NUM associated with PATIENT_NUM
245!What do we do with these?
246!1)If CURRENT_PATIENT_NUM is null ignore mapping
247!2)If CURRENT_PATIENT_NUM is null copy over null as the new patient_num
248!3)Identify the queries that have patient sets with CURRENT_PATIENT_NUM as null and set them to
249   expired? >My preferred<
250select * from old_patient_to_new_filtered where current_patient_num is null; --402
251*/
252
253/***********************************
254 create a view of queries with expired patient mappings
255 ***********************************/
256whenever sqlerror continue;
257truncate table expired_queries_bc_no_map;
258drop table expired_queries_bc_no_map;
259whenever sqlerror exit;
260 
261create global temporary table expired_queries_bc_no_map on commit preserve rows as
262select distinct result_instance_id, query_instance_id
263from old_patient_to_new_filtered
264where current_patient_num is null;
265
266/*eyeball it
267!Number of result_instance_id (queries) that need to be discarded
268select count (distinct result_instance_id) from expired_queries_bc_no_map;
269
270!count of queries/patient sets that are not expired.
271select result_instance_id, count(*) from old_patient_to_new_filtered group by result_instance_id minus
272select result_instance_id, count(*) from old_patient_to_new_filtered
273natural join expired_queries_bc_no_map group by result_instance_id;
274
275!Queries that descriptions that are expired
276select qr.result_instance_id
277     , qr.query_instance_id
278     , qr.start_date
279     , qr.set_size
280     , qr.description
281     , qi.user_id
282     , qi.group_id
283     , qi.delete_flag
284     , qi.status_type_id
285     from blueherondata.qt_query_result_instance@deid qr
286     join expired_queries_bc_no_map eq on qr.result_instance_id = eq.result_instance_id
287     join blueherondata.qt_query_instance@deid qi on qi.query_instance_id = qr.query_instance_id;
288*/
289
290/*Only* create table of needed mappings (ones without an associated expired query)*/
291whenever sqlerror continue;
292truncate table only_needed_mappings;
293drop table only_needed_mappings;
294whenever sqlerror exit;
295
296create table only_needed_mappings as
297select * from old_patient_to_new_filtered pmap
298where pmap.result_instance_id not in
299      (select result_instance_id from expired_queries_bc_no_map);
300     
301
302--Continue to Script 2: create_deid_patient_mapping.sql on the deid database
Note: See TracBrowser for help on using the repository browser.