-
Notifications
You must be signed in to change notification settings - Fork 13
/
fhir_specimen_lab.sql
62 lines (58 loc) · 2.07 KB
/
fhir_specimen_lab.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
SELECT fhir_etl.fn_create_table_patient_dependent('specimen_lab');
-- Lab specimen
WITH lab AS (
SELECT
lab.specimen_id
, MAX(lab.itemid) AS itemid
, MAX(lab.subject_id) AS subject_id
, MAX(lab.charttime) AS charttime
FROM
mimiciv_hosp.labevents lab
GROUP BY
specimen_id
)
, fhir_specimen_lab AS (
SELECT
CAST(lab.specimen_id AS TEXT) AS lab_SPECIMEN_ID
, CAST(lab.charttime AS TIMESTAMPTZ) AS lab_CHARTTIME
, dlab.fluid AS dlab_FLUID
, uuid_generate_v5(ns_specimen.uuid, CAST(lab.specimen_id AS TEXT)) AS uuid_SPECIMEN
, uuid_generate_v5(ns_patient.uuid, CAST(lab.subject_id AS TEXT)) as uuid_SUBJECT_ID
FROM
lab
LEFT JOIN mimiciv_hosp.d_labitems dlab
ON lab.itemid = dlab.itemid
LEFT JOIN fhir_etl.uuid_namespace ns_patient
ON ns_patient.name = 'Patient'
LEFT JOIN fhir_etl.uuid_namespace ns_specimen
ON ns_specimen.name = 'SpecimenLab'
)
INSERT INTO mimic_fhir.specimen_lab
SELECT
uuid_SPECIMEN AS id
, uuid_SUBJECT_ID AS patient_id
, jsonb_strip_nulls(jsonb_build_object(
'resourceType', 'Specimen'
, 'id', uuid_SPECIMEN
, 'meta', jsonb_build_object(
'profile', jsonb_build_array(
'http://mimic.mit.edu/fhir/mimic/StructureDefinition/mimic-specimen'
)
)
, 'identifier', jsonb_build_array(jsonb_build_object(
'value', lab_SPECIMEN_ID
, 'system', 'http://mimic.mit.edu/fhir/mimic/identifier/specimen-lab'
))
, 'type', jsonb_build_object(
'coding', jsonb_build_array(jsonb_build_object(
'code', dlab_FLUID
, 'system', 'http://mimic.mit.edu/fhir/mimic/CodeSystem/mimic-lab-fluid'
))
)
, 'subject', jsonb_build_object('reference', 'Patient/' || uuid_SUBJECT_ID)
, 'collection', jsonb_build_object(
'collectedDateTime', lab_CHARTTIME
)
)) AS fhir
FROM
fhir_specimen_lab;