forked from rohankhera/mimic-omop
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathetl.sql
76 lines (76 loc) · 4.03 KB
/
etl.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
WITH
"admissions_emerged" AS (SELECT subject_id, admission_location, discharge_location, mimic_id, coalesce(edregtime, admittime) AS admittime, dischtime, admission_type, edregtime, diagnosis FROM admissions ),
"admissions" AS (
SELECT subject_id
, admission_location
, discharge_location
, mimic_id as visit_occurrence_id
, admittime::date as visit_start_date
, admittime as visit_start_datetime
, dischtime::date as visit_end_date
, dischtime as visit_end_datetime
, 44818518 as visit_type_concept_id
, admission_type as visit_source_value
, admission_location as admitting_source_value
, discharge_location as discharge_to_source_value
, diagnosis
, LAG(mimic_id) OVER ( PARTITION BY subject_id ORDER BY admittime ASC) as preceding_visit_occurrence_id
FROM admissions_emerged
),
"patients" AS (SELECT subject_id, mimic_id as person_id FROM patients),
"gcpt_admission_type_to_concept" AS (SELECT mimic_id as visit_source_concept_id, admission_type as visit_source_value, visit_concept_id FROM gcpt_admission_type_to_concept),
"gcpt_admission_location_to_concept" AS (SELECT concept_id as admitting_concept_id, mimic_id as admitting_source_concept_id, admission_location FROM gcpt_admission_location_to_concept),
"gcpt_discharge_location_to_concept" AS (SELECT concept_id as discharge_to_concept_id, mimic_id as discharge_to_source_concept_id, discharge_location FROM gcpt_discharge_location_to_concept),
"care_site" as (select care_site_id from :OMOP_SCHEMA.care_site where care_site_name = 'BIDMC') -- Beth Israel hospital for all
INSERT INTO :OMOP_SCHEMA.VISIT_OCCURRENCE
(
visit_occurrence_id
, person_id
, visit_concept_id
, visit_start_date
, visit_start_datetime
, visit_end_date
, visit_end_datetime
, visit_type_concept_id
, provider_id
, care_site_id
, visit_source_value
, visit_source_concept_id
, admitting_concept_id --
, admitting_source_value
, admitting_source_concept_id
, discharge_to_concept_id --
, discharge_to_source_value
, discharge_to_source_concept_id
, preceding_visit_occurrence_id
)
SELECT
admissions.visit_occurrence_id
, patients.person_id
, gcpt_admission_type_to_concept.visit_concept_id
, admissions.visit_start_date
, admissions.visit_start_datetime
, admissions.visit_end_date
, admissions.visit_end_datetime
, admissions.visit_type_concept_id
, null::integer as provider_id
, care_site.care_site_id
, gcpt_admission_type_to_concept.visit_source_value
, gcpt_admission_type_to_concept.visit_source_concept_id
, CASE WHEN diagnosis ~* 'organ donor' THEN 4216643 -- DEAD/EXPIRED
ELSE gcpt_admission_location_to_concept.admitting_concept_id END AS admitting_concept_id --
, CASE WHEN diagnosis ~* 'organ donor' THEN 'DEAD/EXPIRED'
ELSE gcpt_admission_location_to_concept.admission_location END AS admitting_source_value
, gcpt_admission_location_to_concept.admitting_source_concept_id
, CASE WHEN diagnosis ~* 'organ donor' THEN 4022058 --ORGAN DONOR
ELSE gcpt_discharge_location_to_concept.discharge_to_concept_id END AS discharge_to_concept_id --
,CASE WHEN diagnosis ~* 'organ donor' THEN diagnosis
ELSE gcpt_discharge_location_to_concept.discharge_location END AS discharge_to_source_value
, gcpt_discharge_location_to_concept.discharge_to_source_concept_id
, admissions.preceding_visit_occurrence_id
FROM admissions
LEFT JOIN gcpt_admission_location_to_concept USING (admission_location)
LEFT JOIN gcpt_discharge_location_to_concept USING (discharge_location)
LEFT JOIN gcpt_admission_type_to_concept USING (visit_source_value)
LEFT JOIN patients USING (subject_id)
left join care_site ON (1=1);