-
Notifications
You must be signed in to change notification settings - Fork 1.5k
/
Copy pathcharlson.sql
234 lines (234 loc) · 10.1 KB
/
charlson.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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciv_derived.charlson; CREATE TABLE mimiciv_derived.charlson AS
/* ------------------------------------------------------------------ */ /* This query extracts Charlson Comorbidity Index (CCI) based on the */ /* recorded ICD-9 and ICD-10 codes. */ /* Reference for CCI: */ /* (1) Charlson ME, Pompei P, Ales KL, MacKenzie CR. (1987) A new method */ /* of classifying prognostic comorbidity in longitudinal studies: */ /* development and validation.J Chronic Dis; 40(5):373-83. */ /* (2) Charlson M, Szatrowski TP, Peterson J, Gold J. (1994) Validation */ /* of a combined comorbidity index. J Clin Epidemiol; 47(11):1245-51. */ /* */ /* Reference for ICD-9-CM and ICD-10 Coding Algorithms for Charlson */ /* Comorbidities: */ /* (3) Quan H, Sundararajan V, Halfon P, et al. Coding algorithms for */ /* defining Comorbidities in ICD-9-CM and ICD-10 administrative data. */ /* Med Care. 2005 Nov; 43(11): 1130-9. */ /* ------------------------------------------------------------------ */
WITH diag AS (
SELECT
hadm_id,
CASE WHEN icd_version = 9 THEN icd_code ELSE NULL END AS icd9_code,
CASE WHEN icd_version = 10 THEN icd_code ELSE NULL END AS icd10_code
FROM mimiciv_hosp.diagnoses_icd
), com AS (
SELECT
ad.hadm_id, /* Myocardial infarction */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('410', '412')
OR SUBSTR(icd10_code, 1, 3) IN ('I21', 'I22')
OR SUBSTR(icd10_code, 1, 4) = 'I252'
THEN 1
ELSE 0
END
) AS myocardial_infarct, /* Congestive heart failure */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) = '428'
OR SUBSTR(icd9_code, 1, 5) IN ('39891', '40201', '40211', '40291', '40401', '40403', '40411', '40413', '40491', '40493')
OR SUBSTR(icd9_code, 1, 4) BETWEEN '4254' AND '4259'
OR SUBSTR(icd10_code, 1, 3) IN ('I43', 'I50')
OR SUBSTR(icd10_code, 1, 4) IN ('I099', 'I110', 'I130', 'I132', 'I255', 'I420', 'I425', 'I426', 'I427', 'I428', 'I429', 'P290')
THEN 1
ELSE 0
END
) AS congestive_heart_failure, /* Peripheral vascular disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('440', '441')
OR SUBSTR(icd9_code, 1, 4) IN ('0930', '4373', '4471', '5571', '5579', 'V434')
OR SUBSTR(icd9_code, 1, 4) BETWEEN '4431' AND '4439'
OR SUBSTR(icd10_code, 1, 3) IN ('I70', 'I71')
OR SUBSTR(icd10_code, 1, 4) IN ('I731', 'I738', 'I739', 'I771', 'I790', 'I792', 'K551', 'K558', 'K559', 'Z958', 'Z959')
THEN 1
ELSE 0
END
) AS peripheral_vascular_disease, /* Cerebrovascular disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '430' AND '438'
OR SUBSTR(icd9_code, 1, 5) = '36234'
OR SUBSTR(icd10_code, 1, 3) IN ('G45', 'G46')
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'I60' AND 'I69'
OR SUBSTR(icd10_code, 1, 4) = 'H340'
THEN 1
ELSE 0
END
) AS cerebrovascular_disease, /* Dementia */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) = '290'
OR SUBSTR(icd9_code, 1, 4) IN ('2941', '3312')
OR SUBSTR(icd10_code, 1, 3) IN ('F00', 'F01', 'F02', 'F03', 'G30')
OR SUBSTR(icd10_code, 1, 4) IN ('F051', 'G311')
THEN 1
ELSE 0
END
) AS dementia, /* Chronic pulmonary disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '490' AND '505'
OR SUBSTR(icd9_code, 1, 4) IN ('4168', '4169', '5064', '5081', '5088')
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'J40' AND 'J47'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'J60' AND 'J67'
OR SUBSTR(icd10_code, 1, 4) IN ('I278', 'I279', 'J684', 'J701', 'J703')
THEN 1
ELSE 0
END
) AS chronic_pulmonary_disease, /* Rheumatic disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) = '725'
OR SUBSTR(icd9_code, 1, 4) IN ('4465', '7100', '7101', '7102', '7103', '7104', '7140', '7141', '7142', '7148')
OR SUBSTR(icd10_code, 1, 3) IN ('M05', 'M06', 'M32', 'M33', 'M34')
OR SUBSTR(icd10_code, 1, 4) IN ('M315', 'M351', 'M353', 'M360')
THEN 1
ELSE 0
END
) AS rheumatic_disease, /* Peptic ulcer disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('531', '532', '533', '534')
OR SUBSTR(icd10_code, 1, 3) IN ('K25', 'K26', 'K27', 'K28')
THEN 1
ELSE 0
END
) AS peptic_ulcer_disease, /* Mild liver disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('570', '571')
OR SUBSTR(icd9_code, 1, 4) IN ('0706', '0709', '5733', '5734', '5738', '5739', 'V427')
OR SUBSTR(icd9_code, 1, 5) IN ('07022', '07023', '07032', '07033', '07044', '07054')
OR SUBSTR(icd10_code, 1, 3) IN ('B18', 'K73', 'K74')
OR SUBSTR(icd10_code, 1, 4) IN ('K700', 'K701', 'K702', 'K703', 'K709', 'K713', 'K714', 'K715', 'K717', 'K760', 'K762', 'K763', 'K764', 'K768', 'K769', 'Z944')
THEN 1
ELSE 0
END
) AS mild_liver_disease, /* Diabetes without chronic complication */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 4) IN ('2500', '2501', '2502', '2503', '2508', '2509')
OR SUBSTR(icd10_code, 1, 4) IN ('E100', 'E101', 'E106', 'E108', 'E109', 'E110', 'E111', 'E116', 'E118', 'E119', 'E120', 'E121', 'E126', 'E128', 'E129', 'E130', 'E131', 'E136', 'E138', 'E139', 'E140', 'E141', 'E146', 'E148', 'E149')
THEN 1
ELSE 0
END
) AS diabetes_without_cc, /* Diabetes with chronic complication */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 4) IN ('2504', '2505', '2506', '2507')
OR SUBSTR(icd10_code, 1, 4) IN ('E102', 'E103', 'E104', 'E105', 'E107', 'E112', 'E113', 'E114', 'E115', 'E117', 'E122', 'E123', 'E124', 'E125', 'E127', 'E132', 'E133', 'E134', 'E135', 'E137', 'E142', 'E143', 'E144', 'E145', 'E147')
THEN 1
ELSE 0
END
) AS diabetes_with_cc, /* Hemiplegia or paraplegia */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('342', '343')
OR SUBSTR(icd9_code, 1, 4) IN ('3341', '3440', '3441', '3442', '3443', '3444', '3445', '3446', '3449')
OR SUBSTR(icd10_code, 1, 3) IN ('G81', 'G82')
OR SUBSTR(icd10_code, 1, 4) IN ('G041', 'G114', 'G801', 'G802', 'G830', 'G831', 'G832', 'G833', 'G834', 'G839')
THEN 1
ELSE 0
END
) AS paraplegia, /* Renal disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('582', '585', '586', 'V56')
OR SUBSTR(icd9_code, 1, 4) IN ('5880', 'V420', 'V451')
OR SUBSTR(icd9_code, 1, 4) BETWEEN '5830' AND '5837'
OR SUBSTR(icd9_code, 1, 5) IN ('40301', '40311', '40391', '40402', '40403', '40412', '40413', '40492', '40493')
OR SUBSTR(icd10_code, 1, 3) IN ('N18', 'N19')
OR SUBSTR(icd10_code, 1, 4) IN ('I120', 'I131', 'N032', 'N033', 'N034', 'N035', 'N036', 'N037', 'N052', 'N053', 'N054', 'N055', 'N056', 'N057', 'N250', 'Z490', 'Z491', 'Z492', 'Z940', 'Z992')
THEN 1
ELSE 0
END
) AS renal_disease, /* Any malignancy, including lymphoma and leukemia, */ /* except malignant neoplasm of skin. */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) BETWEEN '140' AND '172'
OR SUBSTR(icd9_code, 1, 4) BETWEEN '1740' AND '1958'
OR SUBSTR(icd9_code, 1, 3) BETWEEN '200' AND '208'
OR SUBSTR(icd9_code, 1, 4) = '2386'
OR SUBSTR(icd10_code, 1, 3) IN ('C43', 'C88')
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C00' AND 'C26'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C30' AND 'C34'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C37' AND 'C41'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C45' AND 'C58'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C60' AND 'C76'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C81' AND 'C85'
OR SUBSTR(icd10_code, 1, 3) BETWEEN 'C90' AND 'C97'
THEN 1
ELSE 0
END
) AS malignant_cancer, /* Moderate or severe liver disease */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 4) IN ('4560', '4561', '4562')
OR SUBSTR(icd9_code, 1, 4) BETWEEN '5722' AND '5728'
OR SUBSTR(icd10_code, 1, 4) IN ('I850', 'I859', 'I864', 'I982', 'K704', 'K711', 'K721', 'K729', 'K765', 'K766', 'K767')
THEN 1
ELSE 0
END
) AS severe_liver_disease, /* Metastatic solid tumor */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('196', '197', '198', '199')
OR SUBSTR(icd10_code, 1, 3) IN ('C77', 'C78', 'C79', 'C80')
THEN 1
ELSE 0
END
) AS metastatic_solid_tumor, /* AIDS/HIV */
MAX(
CASE
WHEN SUBSTR(icd9_code, 1, 3) IN ('042', '043', '044')
OR SUBSTR(icd10_code, 1, 3) IN ('B20', 'B21', 'B22', 'B24')
THEN 1
ELSE 0
END
) AS aids
FROM mimiciv_hosp.admissions AS ad
LEFT JOIN diag
ON ad.hadm_id = diag.hadm_id
GROUP BY
ad.hadm_id
), ag AS (
SELECT
hadm_id,
age,
CASE
WHEN age <= 50
THEN 0
WHEN age <= 60
THEN 1
WHEN age <= 70
THEN 2
WHEN age <= 80
THEN 3
ELSE 4
END AS age_score
FROM mimiciv_derived.age
)
SELECT
ad.subject_id,
ad.hadm_id,
ag.age_score,
myocardial_infarct,
congestive_heart_failure,
peripheral_vascular_disease,
cerebrovascular_disease,
dementia,
chronic_pulmonary_disease,
rheumatic_disease,
peptic_ulcer_disease,
mild_liver_disease,
diabetes_without_cc,
diabetes_with_cc,
paraplegia,
renal_disease,
malignant_cancer,
severe_liver_disease,
metastatic_solid_tumor,
aids, /* Calculate the Charlson Comorbidity Score using the original */ /* weights from Charlson, 1987. */
age_score + myocardial_infarct + congestive_heart_failure + peripheral_vascular_disease + cerebrovascular_disease + dementia + chronic_pulmonary_disease + rheumatic_disease + peptic_ulcer_disease + GREATEST(mild_liver_disease, 3 * severe_liver_disease) + GREATEST(2 * diabetes_with_cc, diabetes_without_cc) + GREATEST(2 * malignant_cancer, 6 * metastatic_solid_tumor) + 2 * paraplegia + 2 * renal_disease + 6 * aids AS charlson_comorbidity_index
FROM mimiciv_hosp.admissions AS ad
LEFT JOIN com
ON ad.hadm_id = com.hadm_id
LEFT JOIN ag
ON com.hadm_id = ag.hadm_id