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_medical_history.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.0 KB
Line 
1/* epic_medical_history -- harvest medical history into encounter table
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
7Ref:
8
9  * Clarity Data Dictionary - Epic 2012
10    Catalog (Last updated: 07/14/12)
11    https://galaxy.epic.com/Search/GetFile?url=7900!44!100!1770229
12
13*/
14
15/* Check that we're connected to an Epic CLARITY database. */
16select pat_id from CLARITY.patient where 1 = 0;
17/* Check that views from epic_diag_tx.sql are available. */
18select provider_id from discrete_fact_constants where 1 = 0;
19
20--Create a view of epic patients' past medical history
21create or replace view medical_hx
22as
23  select to_char(mhx.pat_enc_csn_id) as encounter_ide    ,
24    mhx.pat_id as patient_ide                            ,
25    labels.dx_id_scheme || mhx.dx_id as concept_cd       ,
26    mhx.contact_date as start_date                       ,
27    'DiagObs:MEDICAL_HX' as modifier_cd,  -- TODO decide the correct modifier_cd
28    -- keyed by pat_enc_csn_id, line
29    -- using ora_hash(), we can put this into the instance_num
30    -- Probability of hash collision would be 10^-6 with 93 of the same diagnoses
31    -- in the same financial encounter on the same date (if the events were independent).
32    ora_hash(to_char(mhx.pat_enc_csn_id) || ',' || to_char(mhx.line)) instance_num,
33    mhx.contact_date as end_date                         ,
34    mhx.contact_date as update_date                      ,
35    mod(mhx.pat_enc_csn_id, &&heron_etl_chunks)+1 as part,
36    fconst.* -- add in default values for provider_id, valtype_cd etc.
37  from clarity.medical_hx mhx,
38  scheme_labels@deid labels,
39  discrete_fact_constants fconst
40  where mhx.contact_date is not null;
41
42--Threshold tests are commented out for the time being as div by 0
43--would fail with current test data.
44--[Threshold TEST]
45--How many people have had their appendix taken out?
46--select count (distinct medical_hx.patient_ide) as occurrences_by_patient
47--from medical_hx
48--where regexp_like (medical_hx.concept_cd, '^ICD9:54(0|1|2)(\.[:digit:]+)?');
49
50--[Threshold TEST]
51--How many people have had essential hypertension?
52--select count (distinct medical_hx.patient_ide) as occurrences_by_patient,
53--from medical_hx
54--where regexp_like (medical_hx.concept_cd, '^ICD9:401(\.[:digit:]+)?');
55
56--[Threshold TEST]
57--How many people have had diabetes in their past medical history?
58--select count (distinct medical_hx.patient_ide) as occurrences_by_patient,
59--from medical_hx
60--where regexp_like (medical_hx.concept_cd, '^ICD9:2[49|50](\.[:digit:]+)?');
61
62--Create a view of patients' family history
63--Utilizes SQL queries from epic_diag_tx.sql [discrete_fact_constants]
64create or replace view family_hx
65as
66  select to_char(fhx.pat_enc_csn_id) as encounter_ide         ,
67    fhx.pat_id as patient_ide                                 ,
68    'KUMC|FAMILYHISTORYDIAG:'|| fhx.medical_hx_c as concept_cd,
69    fhx.contact_date as start_date                            ,
70    'FamRelation:'||
71      /*TODO decide what do we do when the family relation is not specified.
72        There are instances where the family relation is null
73        which make it unclear who the family relation is or whether
74        the fact was created in error.
75       
76        For now we will assume that 'null' is attributed to some unspecified
77        family member (and not negative history) */
78      (case when fhx.relation_c is null then 'null' else fhx.relation_c end)
79      as modifier_cd                                          ,
80    -- keyed by pat_enc_csn_id, line
81    -- using ora_hash(), we can put this into the instance_num
82    -- Probability of hash collision would be 10^-6 with 93 of the same diagnoses
83    -- in the same financial encounter on the same date (if the events were independent).
84    ora_hash(to_char(fhx.pat_enc_csn_id) || ',' || to_char(fhx.line)) instance_num,
85    fhx.contact_date as end_date                              ,
86    fhx.contact_date as update_date                           ,
87    --fhx.relation_c  as relation                               ,
88    mod(fhx.pat_enc_csn_id, &&heron_etl_chunks)+1 as part     ,
89    fconst.* -- add in default values for provider_id, valtype_cd etc.
90  from clarity.family_hx fhx,
91  discrete_fact_constants fconst
92  --If the family history diagnosis is null then the fact has no meaning
93  --with regard to the patient, ignore
94  where fhx.medical_hx_c is not null;
95
96--[Threshold Test]
97--How many people had a parent with cancer?
98/*with joinfamily as
99(
100select fhx.patient_ide as pat_id,
101       fhd.name_char as illness,
102       frm.c_name as family_member,
103       frm.c_fullname as path
104  from family_hx fhx
105  join family_hx_diag fhd on fhx.concept_cd = fhd.concept_cd
106  join family_relation_modifiers frm on fhx.modifier_cd = frm.c_basecode
107  where fhd.name_char like 'Cancer%' and regexp_like (frm.c_fullname, '\Parent\')
108)
109select count (distinct pat_id) as  from joinfamily;*/
110
111
112--Create a view of epic patients' past surgical history
113--Utilizes SQL queries from epic_diag_tx.sql [discrete_fact_constants]
114create or replace view surgical_hx
115as
116  select to_char(shx.pat_enc_csn_id) as encounter_ide
117       , shx.pat_id as patient_ide
118       , 'KUH|SURG_PROC:'||proc_id as concept_cd
119       , shx.contact_date as start_date
120       , '@' as modifier_cd
121       -- keyed by pat_enc_csn_id, line
122       -- using ora_hash(), we can put this into the instance_num
123       -- Probability of hash collision would be 10^-6 with 93 of the same diagnoses
124       -- in the same financial encounter on the same date (if the events were independent).
125       , ora_hash(to_char(shx.pat_enc_csn_id) || ',' || to_char(shx.line)) instance_num
126       , shx.contact_date as end_date
127       , shx.contact_date as update_date
128       , mod(shx.pat_enc_csn_id, &&heron_etl_chunks)+1 as part
129       , fconst.* -- add in default values for provider_id, valtype_cd etc.
130  from clarity.surgical_hx shx,
131  discrete_fact_constants fconst
132  where shx.contact_date is not null
133    and trim(proc_id) is not null;
Note: See TracBrowser for help on using the repository browser.