-
Notifications
You must be signed in to change notification settings - Fork 47
/
Copy pathetl.sql
118 lines (118 loc) · 4.36 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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
WITH
"gcpt_seq_num_to_concept" as (SELECT seq_num, concept_id as condition_type_concept_id FROM gcpt_seq_num_to_concept),
"icd9_concept" as ( SELECT concept_id, concept_code FROM :OMOP_SCHEMA.concept WHERE vocabulary_id = 'ICD9CM'),
"diag" as (
SELECT
mimic_id as condition_occurrence_id
, subject_id
, hadm_id
, CASE
WHEN ICD9_CODE LIKE 'E%' AND LENGTH(ICD9_CODE) > 4 THEN substring(ICD9_CODE, 1, 4) || '.' || substring(ICD9_CODE, 5)
WHEN ICD9_CODE LIKE 'E%' AND length(ICD9_CODE) = 4 THEN ICD9_CODE
WHEN ICD9_CODE NOT LIKE 'E%' AND length(ICD9_CODE) > 3 THEN substring(ICD9_CODE, 1, 3) || '.' || substring(ICD9_CODE, 4)
WHEN ICD9_CODE NOT LIKE 'E%' AND length(ICD9_CODE) = 3 THEN ICD9_CODE ELSE NULL
END as concept_code
, seq_num
, icd9_code as condition_source_value
FROM diagnoses_icd
WHERE icd9_code IS NOT NULL
),
"snomed_map" as (
SELECT rel.concept_id_1
, min(rel.concept_id_2) AS condition_concept_id
FROM :OMOP_SCHEMA.concept_relationship as rel
JOIN :OMOP_SCHEMA.concept as c1
ON (concept_id_1 = c1.concept_id)
JOIN :OMOP_SCHEMA.concept as c2
ON (concept_id_2 = c2.concept_id)
WHERE rel.relationship_id = 'Maps to'
AND c1.vocabulary_id = 'ICD9CM'
AND c2.vocabulary_id = 'SNOMED'
AND c2.concept_class_id = 'Clinical Finding'
AND c2.standard_concept = 'S'
GROUP BY rel.concept_id_1
),
"admissions" as (SELECT subject_id as hadm_subject_id, hadm_id, mimic_id as visit_occurrence_id, diagnosis, coalesce(edregtime, admittime) as condition_start_datetime, dischtime as condition_end_datetime FROM admissions),
"patients" as (SELECT subject_id, mimic_id as person_id FROM patients),
"adm_diag_cpt" AS (SELECT * FROM gcpt_admissions_diagnosis_to_concept),
"row_to_insert" AS (SELECT
condition_occurrence_id
, person_id
, coalesce(condition_concept_id, 0) as condition_concept_id
, condition_start_datetime::date as condition_start_date
, (condition_start_datetime) AS condition_start_datetime
, condition_end_datetime::date as condition_end_date
, (condition_end_datetime) AS condition_end_datetime
, condition_type_concept_id
, null as stop_reason
, null::bigint as provider_id
, visit_occurrence_id
, null::bigint as visit_detail_id
, condition_source_value
, coalesce(icd9_concept.concept_id,0) as condition_source_concept_id
, null as condition_status_source_value
, null::bigint as condition_status_concept_id
FROM diag
LEFT JOIN icd9_concept USING (concept_code)
LEFT JOIN snomed_map ON (snomed_map.concept_id_1 = icd9_concept.concept_id)
LEFT JOIN gcpt_seq_num_to_concept USING (seq_num)
LEFT JOIN admissions USING (hadm_id)
LEFT JOIN patients USING (subject_id)
UNION ALL
SELECT
nextval('mimic_id_seq') AS condition_occurrence_id
, patients.person_id AS person_id
, coalesce(adm_diag_cpt.concept_id, 0) AS condition_concept_id
, admissions.condition_start_datetime::date AS condition_start_date
, (admissions.condition_start_datetime) AS condition_start_datetime
, admissions.condition_end_datetime::date AS condition_end_date
, (admissions.condition_end_datetime) AS condition_end_datetime
, 42894222 AS condition_type_concept_id --EHR Chief Complaint
, null AS stop_reason
, null AS provider_id
, admissions.visit_occurrence_id AS visit_occurrence_id
, null AS visit_detail_id
, admissions.diagnosis AS condition_source_value
, null AS condition_source_concept_id
, null AS condition_status_source_value
, null AS condition_status_concept_id
FROM admissions
LEFT JOIN patients ON (subject_id = hadm_subject_id)
LEFT JOIN adm_diag_cpt USING (diagnosis))
INSERT INTO :OMOP_SCHEMA.condition_occurrence
(
condition_occurrence_id
, person_id
, condition_concept_id
, condition_start_date
, condition_start_datetime
, condition_end_date
, condition_end_datetime
, condition_type_concept_id
, stop_reason
, provider_id
, visit_occurrence_id
, visit_detail_id
, condition_source_value
, condition_source_concept_id
, condition_status_source_value
, condition_status_concept_id
)
SELECT
condition_occurrence_id
, person_id
, condition_concept_id
, condition_start_date
, condition_start_datetime
, condition_end_date
, condition_end_datetime
, condition_type_concept_id
, stop_reason
, provider_id
, visit_occurrence_id
, visit_detail_id
, condition_source_value
, condition_source_concept_id
, condition_status_source_value
, condition_status_concept_id
FROM row_to_insert ;