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_verify_deid_med_orders.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: 2.0 KB
Line 
1/*
2 * Tests that medical order id numbers are deidentified in the BlueHeron
3 * observation fact table.
4 *
5 * Copyright (c) 2014 University of Kansas Medical Center
6 * part of the HERON* open source codebase; see NOTICE file for license details.
7 * http://informatics.kumc.edu/work/wiki/HERON
8 */
9
10
11--! Make sure we are run from deid and can reach the clarity schema on id
12select * from blueherondata.observation_fact where 1 = 0;
13select * from clarity.order_med@id where 1 = 0;
14
15
16with order_med_ids as
17(
18  select distinct order_med_id from clarity.order_med@id
19  --! TODO decide whether we want to use sample or something more deterministic
20  sample (0.1)
21  union
22  --! Selects 100 order_med_id evenly distributed over the order_med table
23  select distinct order_med_id from
24  (
25    select rownum row_num,
26           order_med_id,
27           count(*) over () total
28    from clarity.order_med@id
29  )
30  where remainder(row_num, trunc(total / 100)) = 0
31),
32
33med_order_instance_nums as
34(
35  --! HACK this test assumes that the MEDICATION_ID instance nums have 5 digits
36  --! appended to them for uniqueness related to the modifiers
37  --! See: epic_meds_transform.sql.
38  --! This test will perform adversely (and should be changed) if MEDICATION_ID
39  --! facts ever remove the appended digits
40  select distinct (case
41                     when modifier_cd like 'MedObs|MAR:%'
42                     then (trunc(instance_num / 1e6))
43                     else (trunc(instance_num / 1e5))
44                   end) instance_num_trunc
45  --! FIX when 5 appended digits are likely removed (#2318)
46  -- select distinct instance_num instance_num_trunc
47  from blueherondata.observation_fact
48  where concept_cd like 'KUH|MEDICATION_ID:%'
49)
50--select * from med_order_instance_nums
51,
52
53med_id_instance_num_matches as
54(
55  select * from med_order_instance_nums
56  join order_med_ids on order_med_id = instance_num_trunc
57)
58
59--select * from med_id_instance_num_matches;
60select case when count(*) > 0 then 1/0 else 1 end from med_id_instance_num_matches;
Note: See TracBrowser for help on using the repository browser.