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_ed_episode_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.4 KB
Line 
1/* epic_ed_episode_transform -- ED EPISODE ID flag indicats ED visit
2
3Copyright (c) 2015 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
7References:
8  * Clarity Data Dictionary - Epic 2010
9  * KUMC Informatics ticket #3083
10*/
11
12select pat_id from clarity.pat_enc_hsp where 1 = 0;
13
14/* A non-null ED_EPISODE_ID can be used as a flag to indicate that a patient
15visited the ED as part of the encounter.
16
17ADT_PAT_CLASS_C includes a code denoting "Emergency", but it this is the _last_
18known class of a patient.  So, if a patient was admitted through the ED, the
19ADT_PAT_CLASS would likely be "Inpatient" (since they were admitted) but
20ED_EPISODE_ID would still be non-null.
21
22PAT_ENC_CSN_ID is the primary key for PAT_ENC_HSP.
23*/
24create or replace view epic_ed_episode_obs as
25select
26  to_char(hsp.pat_enc_csn_id) encounter_ide, to_char(hsp.pat_id) patient_ide,
27  'KUH|ED_EPISODE' concept_cd,
28  coalesce(hsp.hosp_admsn_time, hsp.contact_date) start_date,
29  '@' modifier_cd, hsp.pat_enc_csn_id instance_num,
30  coalesce(hsp.hosp_disch_time, hsp.contact_date) end_date,
31  coalesce(hsp.hosp_admsn_time, hsp.contact_date) update_date,
32  mod(ora_hash(hsp.pat_id), &&heron_etl_chunks)+1 as part,
33  fconst.*
34from clarity.pat_enc_hsp hsp, discrete_fact_constants fconst
35where hsp.ed_episode_id is not null
36;
Note: See TracBrowser for help on using the repository browser.