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_allergy_transform.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: 3.7 KB
Line 
1/* epic_allergy_load -- harvest allergy information from epic as observation
2   facts.
3 
4Copyright (c) 2012 University of Kansas Medical Center
5part of the HERON* open source codebase; see NOTICE file for license details.
6* http://informatics.kumc.edu/work/wiki/HERON
7
8Ref:
9
10  * Clarity Data Dictionary - Epic 2012
11    Catalog (Last updated: 07/14/12)
12    https://galaxy.epic.com/Search/GetFile?url=7900!44!100!1770229
13
14*/
15
16/* Check that we're connected to an Epic CLARITY database. */
17select pat_id from CLARITY.patient where 1 = 0;
18
19--!possible tables to investigate for modifiers
20--select * from clarity.zc_allergen_type;
21--select * from clarity.zc_allergy_level;
22--select * from clarity.zc_allergy_severit;
23
24create or replace view allergy_reaction
25as
26
27with dist_allergy_react_recs as
28
29(select distinct
30    to_char(fab.encounter_ide) as encounter_ide, --no encounters are associated
31                                                 --with allergy records, therefore
32                                                 --the fabricated visits from
33                                                 --epic_i2b2_transform is used
34    al.pat_id as patient_ide                                ,
35    'KUH|ALLERGEN:'||al.allergen_id as concept_cd           ,
36    al.update_date as start_date                            ,
37    'Allergy|Reaction:'||(case when alr.reaction_c is null
38                               then 'na' -- documented allergies with no reactions will be
39                                         -- marked as 'na' this likewise explicitly
40                                         -- referenced in the epic reaction modifier
41                               else to_char(alr.reaction_c)
42                          end) as modifier_cd               ,
43    0 as instance_num                                   
44  from clarity.allergy al
45  join fab_reg_visit fab on al.pat_id = fab.patient_ide
46  left join clarity.allergy_reactions alr on al.allergy_id = alr.allergy_id)
47
48select distinct
49    encounter_ide                             ,
50    patient_ide                               ,
51    concept_cd                                ,
52    start_date                                ,
53    modifier_cd                               ,
54    instance_num                              ,                 
55    start_date as end_date                    ,
56    start_date as update_date                 ,
57    mod(ora_hash(patient_ide), &&heron_etl_chunks)+1 as part ,
58    '@' provider_id                           ,
59    '@' valtype_cd                            ,
60    '@' tval_char                             ,
61    to_number(null) nval_num                  ,
62    null valueflag_cd                         ,
63    null units_cd                             ,
64    null location_cd                          ,
65    to_number(null) confidence_num
66  from dist_allergy_react_recs;
67
68/*
69! All records that have a null encounter_ide, patient_ide, concept_cd,
70! modifier_cd, start_date.  (Should be 0)
71select * from allergy_reaction where encounter_ide is null
72                                  or patient_ide is null
73                                  or concept_cd is null
74                                  or start_date is null
75                                  or modifier_cd is null;
76
77! All records that are not unique over encounter_ide, patient_ide, concept_cd,
78! modifier_cd, start_date, instance_num (Should be 0)
79select * from allergy_reaction ar
80join
81  (select distinct patient_ide from
82  (select count (*), encounter_ide, patient_ide, concept_cd, modifier_cd, start_date, instance_num
83  from allergy_reaction
84  group by encounter_ide, patient_ide, concept_cd, modifier_cd, start_date, instance_num
85  having count (*) > 1)) dup
86on ar.patient_ide = dup.patient_ide
87order by ar.patient_ide, concept_cd, modifier_cd, start_date;
88*/
Note: See TracBrowser for help on using the repository browser.