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_services_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.1 KB
Line 
1/* EPIC to i2b2 Services ETL: transform
2
3Copyright (c) 2013 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
7Load the epic clinical service details into i2b2.
8*/
9
10/* create clinical_services view to build concepts*/
11
12create or replace view observation_fact_services as
13select
14      to_char(hsp.pat_id) patient_ide
15    , to_char(hsp.pat_enc_csn_id) encounter_ide
16          , 'KUMC|VISITDETAIL|HSPSERVICES:'|| service.hosp_serv_c  concept_cd
17    , '@' provider_id
18    , '@' modifier_cd
19    , hsp.pat_enc_csn_id as instance_num
20    , '@' valtype_cd
21    , '@' tval_char
22    , to_number(null) nval_num
23    , null valueflag_cd
24    , null units_cd
25    , null location_cd 
26    , to_number(null) confidence_num
27    , to_date(null) update_date
28    , coalesce(hsp.hosp_admsn_time, hsp.contact_date) as start_date
29    , coalesce(hsp.hosp_disch_time, hsp.contact_date) as end_date
30    , mod(hsp.pat_enc_csn_id, &&heron_etl_chunks)+1 as part
31from CLARITY.pat_enc_hsp hsp
32join CLARITY.zc_pat_service service
33on  service.hosp_serv_c =hsp.hosp_serv_c
34;
Note: See TracBrowser for help on using the repository browser.