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_patient_adt_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: 1.9 KB
Line 
1/* epic_patient_adt_transform -- harvest observation facts related to patient
2   records of [A]dmission [D]ischarge and [T]ransfer in Epic.
3 
4Copyright (c) 2014 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.pat_enc_hsp where 1 = 0;
18
19
20/**
21 * ADT Patient class observations (hospital only):
22 * Inpatient, Outpatient, Emergency, etc.
23 */
24create or replace view epic_hospital_adt_obs
25as
26
27with hospital_adt_class as
28(
29  select
30    to_char(hsp.pat_enc_csn_id)                     as encounter_ide,
31    to_char(hsp.pat_id)                             as patient_ide,
32    'KUH|HOSP_ADT_CLASS:'||hsp.adt_pat_class_c      as concept_cd,
33    coalesce(hsp.hosp_admsn_time, hsp.contact_date) as start_date,
34    coalesce(hsp.hosp_disch_time, hsp.contact_date) as end_date,
35    '@'                                             as modifier_cd,
36    pat_enc_csn_id                                  as instance_num
37  from clarity.pat_enc_hsp hsp
38  --filter out ADT Patient Class with no value
39  where hsp.adt_pat_class_c is not null
40)
41
42select distinct
43    encounter_ide                             ,
44    patient_ide                               ,
45    concept_cd                                ,
46    start_date                                ,
47    modifier_cd                               ,
48    instance_num                              ,               
49    end_date                                  ,
50    start_date as update_date                 ,
51    mod(ora_hash(patient_ide), &&heron_etl_chunks)+1 as part,
52    fconst.*
53  from hospital_adt_class, discrete_fact_constants fconst
54;
Note: See TracBrowser for help on using the repository browser.