-
Notifications
You must be signed in to change notification settings - Fork 13
/
fhir_observation_labevents.sql
194 lines (184 loc) · 8.87 KB
/
fhir_observation_labevents.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
-- Purpose: Generate a FHIR Observation resource from the labevents rows
-- Methods: uuid_generate_v5 --> requires uuid or text input, some inputs cast to text to fit
-- Parameters to potentially speed up generation of big tables
-- SET from_collapse_limit = 24;
-- SET join_collapse_limit = 24;
SELECT fhir_etl.fn_create_table_patient_dependent('observation_labevents');
WITH fhir_observation_labevents AS (
SELECT
CAST(lab.labevent_id AS TEXT) AS lab_LABEVENT_ID
, CAST(lab.itemid AS TEXT) AS lab_ITEMID
, dlab.label AS dlab_LABEL
, CAST(lab.charttime AS TIMESTAMPTZ) AS lab_CHARTTIME
, CAST(lab.storetime AS TIMESTAMPTZ) AS lab_STORETIME
, lab.comments AS lab_COMMENTS
, lab.ref_range_lower AS lab_REF_RANGE_LOWER
, lab.ref_range_upper AS lab_REF_RANGE_UPPER
, CASE WHEN lab.valueuom != ' ' THEN
lab.valueuom
ELSE NULL END AS lab_VALUEUOM
, NULLIF(TRIM(lab.value),'') AS lab_VALUE
, lab.priority AS lab_PRIORITY
-- Parse values with a comparator and pulling out numeric value
, CASE
WHEN lab.valuenum IS NOT NULL THEN lab.valuenum
WHEN value IN ('<1>50', '150>') THEN NULL --UNIQUE entries that are invalid
WHEN value ~ '[a-zA-Z]' THEN NULL -- If any letters are found in the value COLUMN (about )
WHEN value ~ '[^\x00-\x7F]' THEN NULL -- FOR unicode char that ARE IN the value COLUMN (about 4 values)
WHEN value LIKE '%<=%' THEN CAST(split_part(lab.value,'<=',2) AS NUMERIC)
WHEN value LIKE '%<%' THEN CAST(split_part(lab.value,'<',2) AS NUMERIC)
WHEN value LIKE '%>=%' THEN CAST(split_part(lab.value,'>=',2) AS NUMERIC)
WHEN value LIKE '%>%' THEN CAST(split_part(lab.value,'>',2) AS NUMERIC)
ELSE NULL
END as lab_VALUENUM
-- , lab.valuenum AS lab_VALUENUM
, CASE
WHEN value LIKE '%<=%' THEN '<='
WHEN value LIKE '%<%' THEN '<'
WHEN value LIKE '%>=%' THEN '>='
WHEN value LIKE '%>%' THEN '>'
WHEN value LIKE '%GREATER THAN%' THEN '>'
WHEN value LIKE '%LESS THAN%' THEN '<'
ELSE NULL
END as VALUE_COMPARATOR
-- Get lab status from comments (error, corrected, cancelled)
, CASE
WHEN comments ILIKE '%error%' THEN 'entered-in-error'
WHEN comments ILIKE '%corrected%' THEN 'corrected'
WHEN comments ILIKE '%cancel%' THEN 'cancelled'
ELSE 'final'
END AS lab_STATUS
-- interpretation
, interp.fhir_interpretation_code AS interp_FHIR_INTERPRETATION_CODE
, interp.fhir_interpretation_display AS interp_FHIR_INTERPRETATION_DISPLAY
-- reference uuids
, uuid_generate_v5(ns_observation_labs.uuid, CAST(lab.labevent_id AS TEXT)) AS uuid_LABEVENT_ID
, uuid_generate_v5(ns_patient.uuid, CAST(lab.subject_id AS TEXT)) AS uuid_SUBJECT_ID
, uuid_generate_v5(ns_encounter.uuid, CAST(lab.hadm_id AS TEXT)) AS uuid_HADM_ID
, uuid_generate_v5(ns_specimen.uuid, CAST(lab.specimen_id AS TEXT)) AS uuid_SPECIMEN_ID
FROM
mimiciv_hosp.labevents lab
LEFT JOIN mimiciv_hosp.d_labitems dlab
ON lab.itemid = dlab.itemid
LEFT JOIN fhir_etl.uuid_namespace ns_encounter
ON ns_encounter.name = 'Encounter'
LEFT JOIN fhir_etl.uuid_namespace ns_patient
ON ns_patient.name = 'Patient'
LEFT JOIN fhir_etl.uuid_namespace ns_observation_labs
ON ns_observation_labs.name = 'ObservationLabevents'
LEFT JOIN fhir_etl.uuid_namespace ns_specimen
ON ns_specimen.name = 'SpecimenLab'
-- mappings
LEFT JOIN fhir_etl.map_lab_interpretation interp
ON lab.flag = interp.mimic_interpretation
)
INSERT INTO mimic_fhir.observation_labevents
SELECT
uuid_LABEVENT_ID as id
, uuid_SUBJECT_ID AS patient_id
, jsonb_strip_nulls(jsonb_build_object(
'resourceType', 'Observation'
, 'id', uuid_LABEVENT_ID
, 'meta', jsonb_build_object(
'profile', jsonb_build_array(
'http://mimic.mit.edu/fhir/mimic/StructureDefinition/mimic-observation-labevents'
)
)
, 'identifier', jsonb_build_array(jsonb_build_object(
'value', lab_LABEVENT_ID
, 'system', 'http://mimic.mit.edu/fhir/mimic/identifier/observation-labevents'
))
, 'status', lab_STATUS
, 'category', jsonb_build_array(jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://terminology.hl7.org/CodeSystem/observation-category'
, 'code', 'laboratory'
, 'display', 'Laboratory'
))
))
-- Lab test completed
, 'code', jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-d-labitems'
, 'code', lab_ITEMID
, 'display', dlab_LABEL
))
)
, 'subject', jsonb_build_object('reference', 'Patient/' || uuid_SUBJECT_ID)
, 'encounter',
CASE WHEN uuid_HADM_ID IS NOT NULL
THEN jsonb_build_object('reference', 'Encounter/' || uuid_HADM_ID)
ELSE NULL END
, 'effectiveDateTime', lab_CHARTTIME
, 'issued', lab_STORETIME
, 'valueQuantity',
CASE WHEN lab_VALUENUM IS NOT NULL THEN
jsonb_build_object(
'value', lab_VALUENUM
, 'unit', lab_VALUEUOM
, 'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-units'
, 'code', lab_VALUEUOM
, 'comparator', VALUE_COMPARATOR
)
ELSE NULL END
, 'valueString',
CASE WHEN lab_VALUENUM IS NULL AND lab_VALUE IS NOT NULL THEN lab_VALUE
WHEN lab_VALUENUM IS NULL AND lab_VALUE IS NULL THEN lab_COMMENTS
ELSE NULL END
, 'dataAbsentReason', CASE WHEN lab_VALUENUM IS NULL AND lab_VALUE IS NULL AND lab_COMMENTS IS NULL THEN
jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://terminology.hl7.org/CodeSystem/data-absent-reason'
, 'code', 'unknown'
))
)
ELSE NULL END
, 'interpretation',
CASE WHEN interp_FHIR_INTERPRETATION_CODE IS NOT NULL THEN
jsonb_build_array(jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'system', 'http://terminology.hl7.org/CodeSystem/v3-ObservationInterpretation'
, 'code', interp_FHIR_INTERPRETATION_CODE
, 'display', interp_FHIR_INTERPRETATION_DISPLAY
))
))
ELSE NULL END
-- Add clinical notes
, 'note',
CASE WHEN lab_COMMENTS IS NOT NULL THEN
jsonb_build_array(jsonb_build_object(
'text', lab_COMMENTS
))
ELSE NULL END
, 'specimen', jsonb_build_object('reference', 'Specimen/' || uuid_SPECIMEN_ID)
, 'referenceRange',
CASE WHEN (lab_REF_RANGE_LOWER IS NOT NULL) OR (lab_REF_RANGE_UPPER IS NOT NULL) THEN
jsonb_build_array(jsonb_build_object(
'low', CASE WHEN lab_REF_RANGE_LOWER IS NOT NULL THEN
jsonb_strip_nulls(jsonb_build_object(
'value', lab_REF_RANGE_LOWER
, 'unit', lab_VALUEUOM
, 'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-units'
, 'code', lab_VALUEUOM
))
ELSE NULL END
, 'high', CASE WHEN lab_REF_RANGE_UPPER IS NOT NULL THEN
jsonb_strip_nulls(jsonb_build_object(
'value', lab_REF_RANGE_UPPER
, 'unit', lab_VALUEUOM
, 'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-units'
, 'code', lab_VALUEUOM
))
ELSE NULL END
))
ELSE NULL END
, 'extension',
CASE WHEN lab_PRIORITY IS NOT NULL THEN
jsonb_build_array(jsonb_build_object(
'url', 'http://mimic.mit.edu/fhir/mimic/StructureDefinition/lab-priority'
, 'valueString', lab_PRIORITY
))
ELSE NULL END
)) as fhir
FROM
fhir_observation_labevents;