-
Notifications
You must be signed in to change notification settings - Fork 0
/
cohort_extraction.sql
1548 lines (1547 loc) · 178 KB
/
cohort_extraction.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
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
{\rtf1\ansi\ansicpg1252\cocoartf2820
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\paperw11900\paperh16840\margl1440\margr1440\vieww38200\viewh21120\viewkind0
\pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\pardirnatural\partightenfactor0
\f0\fs24 \cf0 # After published the paper\
\
# removed patients who discharged withing first 24 hourse from first heparin dose. We need to remove that patients from our cohort, as we don't know what happened to those patients after dischrging)\
# removed ; danaproid and checked for all possible event_txt s.\
# checked again for drug type - ufh / lmwh\
# checked all possibilities were checked for event_txt.\
\
--#1 PATIENTS WHO WERE ADMINISTRATED BY ANY HEPARING GROUP MEDICATION\
\
WITH \
FULL_COHORT AS\
\
(SELECT pres.subject_id as emar_subject_id, pres.hadm_id as emar_hadm_id, EMAR.emar_id, EMAR.charttime AS emar_charttime, EMAR.scheduletime \
AS emar_scheduletime,EMAR.storetime AS emar_storetime, EMAR.medication, EMAR.event_txt,\
\
PRES.subject_id as pres_subject_id, PRES.hadm_id as pres_hadm_id, pres.pharmacy_id, PRES.starttime AS pres_starttime, PRES.stoptime AS \
pres_stoptime, PRES.drug_type, PRES.drug, PRES.prod_strength, PRES.dose_val_rx, PRES.dose_unit_rx, PRES.form_val_disp, PRES.form_unit_disp, \
PRES.doses_per_24_hrs, PRES.route\
\
FROM `physionet-data.mimic_hosp.emar` EMAR\
LEFT JOIN `physionet-data.mimic_hosp.prescriptions` PRES -- as this 'left join', even rows with null 'hadm_id' s in emar, will be still included in the output.\
ON EMAR.pharmacy_id = PRES.pharmacy_id\
\
WHERE \
-- selecting patients who were prescribed in Heparin group medications\
-- why considered prescriptions? (could have used EMRA?) - difficult to caputure successful administration records in EMAR as there are lot of possible event_txt\
-- but this ended up with removing all records in emar, where pharmacy_id is null. Therefore , had to consider 'medication' in 'emar', instead of 'drug' in 'prescriptions'. Successfull 'event_txt' were consider in latter steps.\
-- But then realized, we considered the data from 'presriptions' table inorder to calssifiy : 'T'/ 'P'/'NA'/'TBD', and only 'T'/ 'P' records will be retained.\
-- Therefore no use of rows with blank 'prescriptions' data. If we used 'medication' to fileter heparingroup medicine, then we will be ende up with lot of blank precrption data, ad there are blank 'pharmacy_id' for lot of rows in 'emar' table.\
-- As we used 'prescription' table data in order to classify : 'T'/ 'P'/'NA'/'TBD', we coniderd 'drug' in prescrptions table to filter the heparin group medications.\
\
(LOWER(PRES.DRUG) LIKE '%heparin%' OR\
LOWER(PRES.DRUG) LIKE '%bemiparin%' OR\
LOWER(PRES.DRUG) LIKE '%dalteparin%' OR\
LOWER(PRES.DRUG) LIKE '%danaparoid%' OR -- remove this. this is non-heparin anticogulant use to treat HIT\
LOWER(PRES.DRUG) LIKE '%enoxaparin%' OR\
LOWER(PRES.DRUG) LIKE '%nadroparin%' OR\
LOWER(PRES.DRUG) LIKE '%parnaparin%' OR\
LOWER(PRES.DRUG) LIKE '%reviparin%' OR\
LOWER(PRES.DRUG) LIKE '%tinzaparin%')\
\
)\
\
--#2 EXTRATCING DITINCT ADMINISTRATIONS (MAINLY CONSIDERING MEDICATION, DOSE AND ROUTE) AND CORRESPONDING ADMINISTRATIONS COUNT, TO DECIDE THERAPEAUTIC OR PROPHYLACTIC (THIS IS ANALYSED BY EXPORTING AS AN EXCEL)\
\
,T_OR_P_ANALYSIS AS --(T - THERAPUEATIC, P - PROPHYLACTIC)\
(SELECT medication, event_txt, drug_type, drug, prod_strength, dose_val_rx, dose_unit_rx, form_val_disp, form_unit_disp, doses_per_24_hrs, route, COUNT((medication, event_txt, drug_type, drug, prod_strength, dose_val_rx, dose_unit_rx, form_val_disp, form_unit_disp, doses_per_24_hrs, route)) AS ROW_COUNT FROM FULL_COHORT\
GROUP BY medication, event_txt, drug_type, drug, prod_strength, dose_val_rx, dose_unit_rx, form_val_disp, form_unit_disp, doses_per_24_hrs, route\
ORDER BY ROW_COUNT DESC\
)\
\
--#3 AFTER ANALYSING IN EXCEL, CLASSIFY PATIENTS\
-- MISSED --> 'O' ( -- CHECK --> 'O')\
-- N/A CASES (HPEARIN FLUSH OR NO ENOUGH DATA) -> '1'\
-- Prophylactic --> '2'\
-- THERAPEUTIC --> '3'\
\
,T_OR_P_CLASSIFY AS \
(SELECT T_OR_P_ANALYSIS.*,\
CASE\
WHEN \
(UPPER(T_OR_P_ANALYSIS.medication) LIKE '%FLUSH%' OR UPPER(T_OR_P_ANALYSIS.drug) LIKE '%FLUSH%') \
THEN 'N/A'\
\
WHEN \
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'DIALYS' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'DWELL' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'IMPELLA' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'FEMORAL VEIN' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'PO' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'NG' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'PO/NG' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'NG/PO' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'TP' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'IP' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'LOCK' OR\
UPPER(T_OR_P_ANALYSIS.ROUTE) LIKE 'DLPICC'\
THEN 'N/A'\
\
WHEN \
(T_OR_P_ANALYSIS.dose_val_rx = '0' OR T_OR_P_ANALYSIS.dose_val_rx IS NULL) AND\
(T_OR_P_ANALYSIS.form_val_disp = '0' OR T_OR_P_ANALYSIS.form_val_disp IS NULL) -- INCORRECT RECORDS WHERE DOSE = 0\
THEN 'N/A'\
\
WHEN \
((UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%UNIT%') OR (UPPER(T_OR_P_ANALYSIS.dose_unit_rx) = 'IU')) AND\
(T_OR_P_ANALYSIS.route = 'IV' OR T_OR_P_ANALYSIS.route = 'IV DRIP' OR T_OR_P_ANALYSIS.route = 'IV BOLUS')\
THEN 'T'\
\
WHEN \
((UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%UNIT%') OR (UPPER(T_OR_P_ANALYSIS.dose_unit_rx) = 'IU')) AND\
(T_OR_P_ANALYSIS.route = 'SC' OR T_OR_P_ANALYSIS.route = 'SUBCUT')\
THEN 'P'\
\
\
WHEN \
UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%MG%' AND\
SAFE_CAST(T_OR_P_ANALYSIS.dose_val_rx AS NUMERIC) <= 40 AND\
T_OR_P_ANALYSIS.doses_per_24_hrs = 1\
THEN 'P' \
\
WHEN \
UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%MG%' AND\
SAFE_CAST(T_OR_P_ANALYSIS.dose_val_rx AS NUMERIC) <= 40 AND\
T_OR_P_ANALYSIS.doses_per_24_hrs = 2\
THEN 'T' \
\
WHEN \
UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%MG%' AND\
SAFE_CAST(T_OR_P_ANALYSIS.dose_val_rx AS NUMERIC) <= 40 AND\
((T_OR_P_ANALYSIS.doses_per_24_hrs IS NULL) OR (T_OR_P_ANALYSIS.doses_per_24_hrs = 0))\
THEN 'N/A' \
\
WHEN \
UPPER(T_OR_P_ANALYSIS.dose_unit_rx) LIKE '%MG%' AND\
SAFE_CAST(T_OR_P_ANALYSIS.dose_val_rx AS NUMERIC) > 40 \
THEN 'T' \
\
\
ELSE \
'TBD' --RECORDS WHICH WERE NOT FALLEN INTO ANY OF THE CATEGORIES\
\
END AS CLASSIFICATION\
FROM T_OR_P_ANALYSIS\
)\
\
--#4 Join of #1 and #3. This classifies each heaprin adminitration as 'T' / 'P' / 'N/A' and extracts reords with 'T' / 'P'.Also this add whether the each heparin administered is LMWH or UFH. \
\
,FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS AS \
(SELECT FULL_COHORT.emar_subject_id, FULL_COHORT.emar_hadm_id, FULL_COHORT.PHARMACY_ID, FULL_COHORT.emar_charttime, FULL_COHORT.emar_scheduletime, \
FULL_COHORT.emar_storetime, FULL_COHORT.medication, FULL_COHORT.event_txt,\
FULL_COHORT.pres_starttime, FULL_COHORT.pres_stoptime, \
T_OR_P_CLASSIFY.*except(medication, event_txt) \
\
,CASE -- UNFRACTIONED OR LMW HEPARIN (TRY WITH 'IN', INSTEAD OF DIFFERENT 'OR')\
\
WHEN (lower(T_OR_P_CLASSIFY.DRUG) like 'heparin%') --T_OR_P_CLASSIFY.DRUG in ( 'Heparin', 'Heparin Sodium', 'heparin (porcine)', 'Heparin CRRT', 'Heparin (IABP)', "Heparin (Impella)", "Heparin (Hemodialysis)")) \
THEN\
'UFH' --UNFRACTIONED HEPARIN\
\
WHEN ((lower(T_OR_P_CLASSIFY.DRUG) like '%enoxaparin%') or (lower(T_OR_P_CLASSIFY.DRUG) like '%dalteparin%')) --T_OR_P_CLASSIFY.DRUG in ( 'Heparin', 'Heparin Sodium', 'heparin (porcine)', 'Heparin CRRT', 'Heparin (IABP)', "Heparin (Impella)", "Heparin (Hemodialysis)")) \
THEN\
'LMWH' --UNFRACTIONED HEPARIN\
\
ELSE\
'NA' -- LOW MOLECULAR WEIGHT HEPARIN # Enoxaparin, Dalteparin\
\
END AS HEPARIN_TYPE\
\
, row_number() over(\
partition by emar_hadm_id\
order by emar_charttime asc, classification desc) as rn\
# earliest event appraes first. In next step, only rn=1 records will be ratained. Whey we do this? because some hadm_ids have multiple first_hep admin records in emar. For eaxmple, for emar_hadm_id - 25731558 - had two first hep admin records, where classification / treatment_type of one is 'P' and other one is 'T' - When we partition as in above line, we take the first record (if have multiple first records) with 'T' (as we filtered 'T' or 'P' by descending order)\
\
FROM FULL_COHORT\
LEFT JOIN T_OR_P_CLASSIFY --This join links hadm_id with the classification category(T, P or N/A)\
ON\
(\
\
((FULL_COHORT.medication = T_OR_P_CLASSIFY.medication) OR (FULL_COHORT.medication is null and T_OR_P_CLASSIFY.medication is null)) AND\
((FULL_COHORT.event_txt = T_OR_P_CLASSIFY.event_txt) OR (FULL_COHORT.event_txt is null and T_OR_P_CLASSIFY.event_txt is null)) AND\
((FULL_COHORT.drug_type = T_OR_P_CLASSIFY.drug_type) OR (FULL_COHORT.drug_type is null and T_OR_P_CLASSIFY.drug_type is null)) AND\
((FULL_COHORT.drug = T_OR_P_CLASSIFY.drug) OR (FULL_COHORT.drug is null and T_OR_P_CLASSIFY.drug is null)) AND\
((FULL_COHORT.prod_strength = T_OR_P_CLASSIFY.prod_strength) OR (FULL_COHORT.prod_strength is null and T_OR_P_CLASSIFY.prod_strength is null)) AND\
((FULL_COHORT.dose_val_rx = T_OR_P_CLASSIFY.dose_val_rx) OR (FULL_COHORT.dose_val_rx is null and T_OR_P_CLASSIFY.dose_val_rx is null)) AND\
((FULL_COHORT.dose_unit_rx = T_OR_P_CLASSIFY.dose_unit_rx) OR (FULL_COHORT.dose_unit_rx is null and T_OR_P_CLASSIFY.dose_unit_rx is null)) AND\
((FULL_COHORT.form_val_disp = T_OR_P_CLASSIFY.form_val_disp) OR (FULL_COHORT.form_val_disp is null and T_OR_P_CLASSIFY.form_val_disp is null)) AND\
((FULL_COHORT.form_unit_disp = T_OR_P_CLASSIFY.form_unit_disp) OR (FULL_COHORT.form_unit_disp is null and T_OR_P_CLASSIFY.form_unit_disp is null)) AND\
((FULL_COHORT.doses_per_24_hrs = T_OR_P_CLASSIFY.doses_per_24_hrs) OR (FULL_COHORT.doses_per_24_hrs is null and T_OR_P_CLASSIFY.doses_per_24_hrs is null)) AND\
((FULL_COHORT.route = T_OR_P_CLASSIFY.route) OR (FULL_COHORT.route is null and T_OR_P_CLASSIFY.route is null)) \
\
)\
\
WHERE (T_OR_P_CLASSIFY.CLASSIFICATION = 'T' OR T_OR_P_CLASSIFY.CLASSIFICATION = 'P') -- There are two 'TBD' records for emar_hadm_id - 21582209, but no need to bother about it, as there were rows with 'T' for that emar_hadm_id, before 'TBD' records (later we filtered only the first record of each emar_hadm_id)\
AND\
# condition 1 - coz for some hadm_ids, all hep related records from emar table, had event_txt for unsuccessful administrations.\
# This issue had hadm_id count = 149 (ex: hadm_id -24975936, 29137563)\
\
\
#To check these 149 hadm_ids,\
/*\
SELECT distinct emar_hadm_id from FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS\
where event_txt IN ("Delayed Administered" , "Stopped", "Not Given", "Stopped - Unscheduled", "Hold Dose", "Documented in O.R. Holding") and \
\
emar_hadm_id not in\
(SELECT distinct emar_hadm_id from FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS\
where \
event_txt IN ("Administered" , "Confirmed", "Started", "Restarted", "Administered in Other Location", " in Other Location"))\
*/\
\
# All events - Administered , Delayed Administered , Stopped , Confirmed , Not Given , Restarted , Stopped - Unscheduled , Hold Dose, Started , in Other Location , Administered in Other Location , Documented in O.R. Holding\
\
# sucessful - Administered ,Confirmed , Restarted , Started , in Other Location , Administered in Other Location\
\
# unsucessful - Delayed Administered , Stopped , Not Given ,Stopped - Unscheduled , Hold Dose , Documented in O.R. Holding\
\
# all - distinct hadm_id = 23211\
# row count - 346585\
\
# successful - distinct hadm_id = 23062\
# row count - 343145\
\
# unsuccessful - distinct hadm_id = 2344\
# row count - 3439\
\
-- event_txt that reflects sucessful administrations - may be some others as well.\
\
-- FULL_COHORT.event_txt IN ("Administered" , "Confirmed", "Started", "Restarted", "Administered in Other Location", " in Other Location", "Delayed Administered", "Delayed Started")\
\
\
(\
trim(FULL_COHORT.event_txt) not in ('Hold Dose', 'Not Confirmed', 'Removed', 'Not Started', 'Not Applied', 'Delayed', 'Not Assessed','Documented in O.R. Holding', 'Flushed in Other Location', 'Delayed Flushed', 'Delayed Not Confirmed', 'Flushed','Not Given', 'Delayed Not Started', 'Stopped - Unscheduled', 'Stopped in Other Location','Stopped - Unscheduled in Other Location', 'Stopped As Directed', 'Delayed Stopped', 'Stopped', 'Delayed Stopped As Directed', 'Infusion Reconciliation', 'Infusion Reconciliation Not Done')\
\
or FULL_COHORT.event_txt is null\
\
)\
AND\
\
# condition 2 - coz in returned cohort, in emar, medications are sometimes non hep. This happened coz we just did a left join from presciptions to emar on pharmacy_id, and we only considered hep drugs in prescriptions (step 1 - full cohort). So in this case, hep related pharmacy_id in prescriptions table may have either in hep related medication or non hep medication (rarely) for the same pharmacy_id in emar.\
# As we are checking for hep admins from emar table (coz charttimes are not mentioned in prescriptions) , administrations other than heparin should be excluded. \
\
# hadm_id count - 69 (but some of these hadm_id may have hep medications too in emar), rows - 92 (when exclude condition 1)\
# ex - pharmacy_id - 66443739\
# to check rows\
/*select * from FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS\
where \
(LOWER(medication) NOT LIKE '%heparin%' AND\
LOWER(medication) NOT LIKE '%bemiparin%' AND\
LOWER(medication) NOT LIKE '%dalteparin%' AND\
--LOWER(medication) NOT LIKE '%danaparoid%' AND\
LOWER(medication) NOT LIKE '%enoxaparin%' AND\
LOWER(medication) NOT LIKE '%nadroparin%' AND\
LOWER(medication) NOT LIKE '%parnaparin%' AND\
LOWER(medication) NOT LIKE '%reviparin%' AND\
LOWER(medication) NOT LIKE '%tinzaparin%')\
*/\
\
# 36 hadm_ids didn't have any hep medication in emar, though the drugs prescibed in presciptions table\
# To check for those hadm_ids,\
/*\
select distinct emar_hadm_id from FULL_COHORT\
where emar_hadm_id not in\
(select distinct emar_hadm_id from FULL_COHORT\
where\
(LOWER(medication) LIKE '%heparin%' OR\
LOWER(medication) LIKE '%bemiparin%' OR\
LOWER(medication) LIKE '%dalteparin%' OR\
LOWER(medication) LIKE '%danaparoid%' OR\
LOWER(medication) LIKE '%enoxaparin%' OR\
LOWER(medication) LIKE '%nadroparin%' OR\
LOWER(medication) LIKE '%parnaparin%' OR\
LOWER(medication) LIKE '%reviparin%' OR\
LOWER(medication) LIKE '%tinzaparin%'))\
*/\
\
( LOWER(FULL_COHORT.medication) LIKE '%heparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%bemiparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%dalteparin%' OR\
--LOWER(FULL_COHORT.medication) LIKE '%danaparoid%' OR -- remove this. this is non-heparin anticogulant use to treat HIT\
LOWER(FULL_COHORT.medication) LIKE '%enoxaparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%nadroparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%parnaparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%reviparin%' OR\
LOWER(FULL_COHORT.medication) LIKE '%tinzaparin%')\
\
)\
\
#--------------------------------------------------------------------------------------------------------------------------------------------------\
\
# We need to Take first heaprin administration for each hospital admission. No null 'emar_hadm_id' s in 'FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS', as considered 'hadm_id' fomr 'prescriptions' table. in 'full_cohort' at very begining (no null 'hadm_id'/'subject_id'/'pharmacy_id' in 'prescriptions' table).\
\
, first_hep AS -- take first heaprin admin for each hadm_id (this admin can happen before admitting to icu, or within the icu - may be in the first icu stay or during a subsequent icu stay)\
(\
select emar_hadm_id, emar_charttime as hep_start, classification as treatment_types, heparin_type as hep_types, event_txt, drug\
from FULL_COHORT_HEPARIN_ADMINISTERED_WITHOUT_CONSIDERING_PLATELETS\
where rn = 1\
)\
\
#-------------------------\
\
, first_icu_before_first_hep as --here we get icu stay Intime of the corresponding icustay, where the first heaparin dose was given, We cannot get stay_ID of the particular icu stay, because of the way we grouped. We do it in the next step.\
\
(\
select hadm_id, min(icu.intime) as icu_in_time_first_hep from first_hep \
\
--to get the first icu stay details (when the admission has multiple icustays) where the patient received first heparin dose DURING that icustay \
\
left join `physionet-data.mimic_icu.icustays` icu\
on first_hep.emar_hadm_id = icu.hadm_id\
\
where hep_start BETWEEN intime AND outtime --heparin dose reecived after the patient was admitted to the ICU, and before dischargning from ICU\
\
group by hadm_id\
)\
\
#-------------------------\
\
, first_hep_with_hep_type_and_treatment_type_dermographics as --# one row per one hadm_id, , row count - 13416, distcint hadm_id count - 13416\
(\
select \
\
ADM.subject_id AS subject_id\
\
-- icu stay-related data of first heparin dose\
, first_icu_before_first_hep.hadm_id, stay_id, hep_start, icu_in_time_first_hep, ICU.outtime as icu_out_time_first_hep, ICU.first_careunit , ICU.last_careunit, adm.admittime, adm.dischtime\
\
-- first heparin administration related details - treatmemt type(theraputic/ prophylactic) and heaprin type (UFH/ LMWH) of 1st dose of heparin admin for each hadm_id\
, treatment_types, hep_types, event_txt, drug\
\
-- admission details\
, ADM.admittime, ADM.dischtime, ADM.admission_type, ADM.admission_location, ADM.hospital_expire_flag --IF '1', PATIENT DIED DURING THAT ADMISSION\
\
-- Join dermographics\
, PAT.gender, PAT.anchor_age\
\
from first_icu_before_first_hep -- to get icu intime of first_hep admin of each hadm_id\
left join first_hep -- to get 'first_hep_admin_time' of each hadm_id\
on first_icu_before_first_hep.hadm_id = first_hep.emar_hadm_id \
\
left join `physionet-data.mimic_icu.icustays` ICU\
on first_icu_before_first_hep.hadm_id = icu.hadm_id\
\
left join `physionet-data.mimic_core.admissions` ADM\
on first_icu_before_first_hep.hadm_id = adm.hadm_id\
\
left join `physionet-data.mimic_core.patients` PAT\
on ADM.subject_id = PAT.subject_id\
\
where\
(first_icu_before_first_hep.icu_in_time_first_hep = icu.intime)\
and\
TIMESTAMP_DIFF(adm.dischtime, first_hep.hep_start, HOUR) > 24 -- removed patients who were discharged within 24 hours since first heparin dose.\
\
)\
\
#-------------------------------------------------------------------------------------------------------------------------------------------\
\
#5 - This joins #4 with platelet count records.\
# This joins each hep admin with platelet count records for that hadm_id\
# multiple rows per one hadm_id\
\
\
, JOIN_HEAPRIN_PLATELET_LEFT AS --ALL hadm_id s had at least one platelet count record. In case, if a patient wouldn't have any platelet count record, that patient was assumed to be HIT Negative.\
(\
\
SELECT HEP_ADMIN_COHORT.hadm_id,\
PLATLETS.charttime as p_charttime, PLATLETS.platelet\
\
FROM first_hep_with_hep_type_and_treatment_type_dermographics HEP_ADMIN_COHORT\
\
left join `physionet-data.mimic_core.admissions` adm -- 'hadm_id' was null most of the time in `mimic_derived.complete_blood_count` but not ;'subject_id'. Therefore, 'subject_id' ahd to be used when do join of `mimic_derived.complete_blood_count`. To get the 'subject_id', we had to use 'admissions' table.\
\
on HEP_ADMIN_COHORT.hadm_id = adm.hadm_id\
\
LEFT JOIN `physionet-data.mimic_derived.complete_blood_count` as PLATLETS \
ON adm.subject_id = PLATLETS.subject_id \
\
where platelet is not null --some records have null platelet count readings. remove them\
and PLATLETS.charttime BETWEEN adm.admittime AND adm.dischtime -- to remove other hadm_ids of particular subject_ids\
)\
\
#------------------------------------------------------------------------------------------------------------------------------------------------\
\
# check for vital signs - labs (hosp), bg (hosp), bg_art (hosp), vitalsigns (icu), GCS (icu)\
\
# 28682905 - had multiple icu stays, and first hep was given later - not in first icu stay\
# 20084622 - first hep was given before first icustay\
# 21362779 - had multiple icu stays, first hep was given during first icustay\
\
, item_id_to_name_without_specimen AS -- convert from itemid into a meaningful column full blood count related parameters in labevents\
(\
SELECT\
MAX(subject_id) as subject_id\
, MAX(hadm_id) as hadm_id\
, specimen_id\
, MAX(charttime) as charttime\
-- convert from itemid into a meaningful column\
, MAX(CASE WHEN itemid = 51221 THEN valuenum ELSE NULL END) AS hematocrit\
, MAX(CASE WHEN itemid = 51221 THEN ref_range_lower ELSE NULL END) AS hematocrit_ref_range_lower\
, MAX(CASE WHEN itemid = 51221 THEN ref_range_upper ELSE NULL END) AS hematocrit_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51222 THEN valuenum ELSE NULL END) AS hemoglobin\
, MAX(CASE WHEN itemid = 51222 THEN ref_range_lower ELSE NULL END) AS hemoglobin_ref_range_lower\
, MAX(CASE WHEN itemid = 51222 THEN ref_range_upper ELSE NULL END) AS hemoglobin_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51265 THEN valuenum ELSE NULL END) AS platelets\
, MAX(CASE WHEN itemid = 51265 THEN ref_range_lower ELSE NULL END) AS platelets_ref_range_lower\
, MAX(CASE WHEN itemid = 51265 THEN ref_range_upper ELSE NULL END) AS platelets_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51301 THEN valuenum ELSE NULL END) AS wbc\
, MAX(CASE WHEN itemid = 51301 THEN ref_range_lower ELSE NULL END) AS wbc_ref_range_lower\
, MAX(CASE WHEN itemid = 51301 THEN ref_range_upper ELSE NULL END) AS wbc_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50862 THEN valuenum ELSE NULL END) AS albumin\
, MAX(CASE WHEN itemid = 50862 THEN ref_range_lower ELSE NULL END) AS albumin_ref_range_lower\
, MAX(CASE WHEN itemid = 50862 THEN ref_range_upper ELSE NULL END) AS albumin_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50930 THEN valuenum ELSE NULL END) AS globulin\
, MAX(CASE WHEN itemid = 50930 THEN ref_range_lower ELSE NULL END) AS globulin_ref_range_lower\
, MAX(CASE WHEN itemid = 50930 THEN ref_range_upper ELSE NULL END) AS globulin_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50976 THEN valuenum ELSE NULL END) AS total_protein\
, MAX(CASE WHEN itemid = 50976 THEN ref_range_lower ELSE NULL END) AS total_protein_ref_range_lower\
, MAX(CASE WHEN itemid = 50976 THEN ref_range_upper ELSE NULL END) AS total_protein_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50868 THEN valuenum ELSE NULL END) AS aniongap\
, MAX(CASE WHEN itemid = 50868 THEN ref_range_lower ELSE NULL END) AS aniongap_ref_range_lower\
, MAX(CASE WHEN itemid = 50868 THEN ref_range_upper ELSE NULL END) AS aniongap_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50882 THEN valuenum ELSE NULL END) AS bicarbonate\
, MAX(CASE WHEN itemid = 50882 THEN ref_range_lower ELSE NULL END) AS bicarbonate_ref_range_lower\
, MAX(CASE WHEN itemid = 50882 THEN ref_range_upper ELSE NULL END) AS bicarbonate_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51006 THEN valuenum ELSE NULL END) AS bun\
, MAX(CASE WHEN itemid = 51006 THEN ref_range_lower ELSE NULL END) AS bun_ref_range_lower\
, MAX(CASE WHEN itemid = 51006 THEN ref_range_upper ELSE NULL END) AS bun_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50893 THEN valuenum ELSE NULL END) AS calcium\
, MAX(CASE WHEN itemid = 50893 THEN ref_range_lower ELSE NULL END) AS calcium_ref_range_lower\
, MAX(CASE WHEN itemid = 50893 THEN ref_range_upper ELSE NULL END) AS calcium_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50902 THEN valuenum ELSE NULL END) AS chloride\
, MAX(CASE WHEN itemid = 50902 THEN ref_range_lower ELSE NULL END) AS chloride_ref_range_lower\
, MAX(CASE WHEN itemid = 50902 THEN ref_range_upper ELSE NULL END) AS chloride_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50912 THEN valuenum ELSE NULL END) AS creatinine\
, MAX(CASE WHEN itemid = 50912 THEN ref_range_lower ELSE NULL END) AS creatinine_ref_range_lower\
, MAX(CASE WHEN itemid = 50912 THEN ref_range_upper ELSE NULL END) AS creatinine_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50931 THEN valuenum ELSE NULL END) AS glucose\
, MAX(CASE WHEN itemid = 50931 THEN ref_range_lower ELSE NULL END) AS glucose_ref_range_lower\
, MAX(CASE WHEN itemid = 50931 THEN ref_range_upper ELSE NULL END) AS glucose_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50983 THEN valuenum ELSE NULL END) AS sodium\
, MAX(CASE WHEN itemid = 50983 THEN ref_range_lower ELSE NULL END) AS sodium_ref_range_lower\
, MAX(CASE WHEN itemid = 50983 THEN ref_range_upper ELSE NULL END) AS sodium_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50971 THEN valuenum ELSE NULL END) AS potassium\
, MAX(CASE WHEN itemid = 50971 THEN ref_range_lower ELSE NULL END) AS potassium_ref_range_lower\
, MAX(CASE WHEN itemid = 50971 THEN ref_range_upper ELSE NULL END) AS potassium_ref_range_upper\
\
, MAX(CASE WHEN itemid = 52069 THEN valuenum ELSE NULL END) AS abs_basophils\
, MAX(CASE WHEN itemid = 52069 THEN ref_range_lower ELSE NULL END) AS abs_basophils_ref_range_lower\
, MAX(CASE WHEN itemid = 52069 THEN ref_range_upper ELSE NULL END) AS abs_basophils_ref_range_upper\
\
, MAX(CASE WHEN itemid = 52073 THEN valuenum ELSE NULL END) AS abs_eosinophils\
, MAX(CASE WHEN itemid = 52073 THEN ref_range_lower ELSE NULL END) AS abs_eosinophils_ref_range_lower\
, MAX(CASE WHEN itemid = 52073 THEN ref_range_upper ELSE NULL END) AS abs_eosinophils_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51133 THEN valuenum ELSE NULL END) AS abs_lymphocytes\
, MAX(CASE WHEN itemid = 51133 THEN ref_range_lower ELSE NULL END) AS abs_lymphocytes_ref_range_lower\
, MAX(CASE WHEN itemid = 51133 THEN ref_range_upper ELSE NULL END) AS abs_lymphocytes_ref_range_upper\
\
, MAX(CASE WHEN itemid = 52074 THEN valuenum ELSE NULL END) AS abs_monocytes\
, MAX(CASE WHEN itemid = 52074 THEN ref_range_lower ELSE NULL END) AS abs_monocytes_ref_range_lower\
, MAX(CASE WHEN itemid = 52074 THEN ref_range_upper ELSE NULL END) AS abs_monocytes_ref_range_upper\
\
, MAX(CASE WHEN itemid = 52075 THEN valuenum ELSE NULL END) AS abs_neutrophils\
, MAX(CASE WHEN itemid = 52075 THEN ref_range_lower ELSE NULL END) AS abs_neutrophils_ref_range_lower\
, MAX(CASE WHEN itemid = 52075 THEN ref_range_upper ELSE NULL END) AS abs_neutrophils_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51143 THEN valuenum ELSE NULL END) AS atyps\
, MAX(CASE WHEN itemid = 51143 THEN ref_range_lower ELSE NULL END) AS atyps_ref_range_lower\
, MAX(CASE WHEN itemid = 51143 THEN ref_range_upper ELSE NULL END) AS atyps_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51144 THEN valuenum ELSE NULL END) AS bands\
, MAX(CASE WHEN itemid = 51144 THEN ref_range_lower ELSE NULL END) AS bands_ref_range_lower\
, MAX(CASE WHEN itemid = 51144 THEN ref_range_upper ELSE NULL END) AS bands_ref_range_upper\
\
, MAX(CASE WHEN itemid = 52135 THEN valuenum ELSE NULL END) AS imm_granulocytes\
, MAX(CASE WHEN itemid = 52135 THEN ref_range_lower ELSE NULL END) AS imm_granulocytes_ref_range_lower\
, MAX(CASE WHEN itemid = 52135 THEN ref_range_upper ELSE NULL END) AS imm_granulocytes_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51251 THEN valuenum ELSE NULL END) AS metas\
, MAX(CASE WHEN itemid = 51251 THEN ref_range_lower ELSE NULL END) AS metas_ref_range_lower\
, MAX(CASE WHEN itemid = 51251 THEN ref_range_upper ELSE NULL END) AS metas_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51257 THEN valuenum ELSE NULL END) AS nrbc\
, MAX(CASE WHEN itemid = 51257 THEN ref_range_lower ELSE NULL END) AS nrbc_ref_range_lower\
, MAX(CASE WHEN itemid = 51257 THEN ref_range_upper ELSE NULL END) AS nrbc_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51196 THEN valuenum ELSE NULL END) AS d_dimer\
, MAX(CASE WHEN itemid = 51196 THEN ref_range_lower ELSE NULL END) AS d_dimer_ref_range_lower\
, MAX(CASE WHEN itemid = 51196 THEN ref_range_upper ELSE NULL END) AS d_dimer_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51214 THEN valuenum ELSE NULL END) AS fibrinogen\
, MAX(CASE WHEN itemid = 51214 THEN ref_range_lower ELSE NULL END) AS fibrinogen_ref_range_lower\
, MAX(CASE WHEN itemid = 51214 THEN ref_range_upper ELSE NULL END) AS fibrinogen_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51297 THEN valuenum ELSE NULL END) AS thrombin\
, MAX(CASE WHEN itemid = 51297 THEN ref_range_lower ELSE NULL END) AS thrombin_ref_range_lower\
, MAX(CASE WHEN itemid = 51297 THEN ref_range_upper ELSE NULL END) AS thrombin_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51237 THEN valuenum ELSE NULL END) AS inr\
, MAX(CASE WHEN itemid = 51237 THEN ref_range_lower ELSE NULL END) AS inr_ref_range_lower\
, MAX(CASE WHEN itemid = 51237 THEN ref_range_upper ELSE NULL END) AS inr_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51274 THEN valuenum ELSE NULL END) AS pt\
, MAX(CASE WHEN itemid = 51274 THEN ref_range_lower ELSE NULL END) AS pt_ref_range_lower\
, MAX(CASE WHEN itemid = 51274 THEN ref_range_upper ELSE NULL END) AS pt_ref_range_upper\
\
, MAX(CASE WHEN itemid = 51275 THEN valuenum ELSE NULL END) AS ptt\
, MAX(CASE WHEN itemid = 51275 THEN ref_range_lower ELSE NULL END) AS ptt_ref_range_lower\
, MAX(CASE WHEN itemid = 51275 THEN ref_range_upper ELSE NULL END) AS ptt_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50861 THEN valuenum ELSE NULL END) AS alt\
, MAX(CASE WHEN itemid = 50861 THEN ref_range_lower ELSE NULL END) AS alt_ref_range_lower\
, MAX(CASE WHEN itemid = 50861 THEN ref_range_upper ELSE NULL END) AS alt_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50863 THEN valuenum ELSE NULL END) AS alp\
, MAX(CASE WHEN itemid = 50863 THEN ref_range_lower ELSE NULL END) AS alp_ref_range_lower\
, MAX(CASE WHEN itemid = 50863 THEN ref_range_upper ELSE NULL END) AS alp_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50878 THEN valuenum ELSE NULL END) AS ast\
, MAX(CASE WHEN itemid = 50878 THEN ref_range_lower ELSE NULL END) AS ast_ref_range_lower\
, MAX(CASE WHEN itemid = 50878 THEN ref_range_upper ELSE NULL END) AS ast_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50867 THEN valuenum ELSE NULL END) AS amylase\
, MAX(CASE WHEN itemid = 50867 THEN ref_range_lower ELSE NULL END) AS amylase_ref_range_lower\
, MAX(CASE WHEN itemid = 50867 THEN ref_range_upper ELSE NULL END) AS amylase_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50885 THEN valuenum ELSE NULL END) AS bilirubin_total\
, MAX(CASE WHEN itemid = 50885 THEN ref_range_lower ELSE NULL END) AS bilirubin_total_ref_range_lower\
, MAX(CASE WHEN itemid = 50885 THEN ref_range_upper ELSE NULL END) AS bilirubin_total_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50883 THEN valuenum ELSE NULL END) AS bilirubin_direct\
, MAX(CASE WHEN itemid = 50883 THEN ref_range_lower ELSE NULL END) AS bilirubin_direct_ref_range_lower\
, MAX(CASE WHEN itemid = 50883 THEN ref_range_upper ELSE NULL END) AS bilirubin_direct_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50884 THEN valuenum ELSE NULL END) AS bilirubin_indirect\
, MAX(CASE WHEN itemid = 50884 THEN ref_range_lower ELSE NULL END) AS bilirubin_indirect_ref_range_lower\
, MAX(CASE WHEN itemid = 50884 THEN ref_range_upper ELSE NULL END) AS bilirubin_indirect_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50910 THEN valuenum ELSE NULL END) AS ck_cpk\
, MAX(CASE WHEN itemid = 50910 THEN ref_range_lower ELSE NULL END) AS ck_cpk_ref_range_lower\
, MAX(CASE WHEN itemid = 50910 THEN ref_range_upper ELSE NULL END) AS ck_cpk_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50911 THEN valuenum ELSE NULL END) AS ck_mb\
, MAX(CASE WHEN itemid = 50911 THEN ref_range_lower ELSE NULL END) AS ck_mb_ref_range_lower\
, MAX(CASE WHEN itemid = 50911 THEN ref_range_upper ELSE NULL END) AS ck_mb_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50927 THEN valuenum ELSE NULL END) AS ggt\
, MAX(CASE WHEN itemid = 50927 THEN ref_range_lower ELSE NULL END) AS ggt_ref_range_lower\
, MAX(CASE WHEN itemid = 50927 THEN ref_range_upper ELSE NULL END) AS ggt_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50954 THEN valuenum ELSE NULL END) AS ld_ldh\
, MAX(CASE WHEN itemid = 50954 THEN ref_range_lower ELSE NULL END) AS ld_ldh_ref_range_lower\
, MAX(CASE WHEN itemid = 50954 THEN ref_range_upper ELSE NULL END) AS ld_ldh_ref_range_upper\
\
-- blood gases (vital signs in mimic derived_first24h_bg)\
\
--, MAX(CASE WHEN itemid = 52033 THEN value ELSE NULL END) AS specimen -- when this value = 'ART', can use for bg_art\
\
, MAX(CASE WHEN itemid = 50813 THEN valuenum ELSE NULL END) AS lactate_bg, MAX(CASE WHEN itemid = 50813 THEN ref_range_lower ELSE NULL END) AS lactate_bg_ref_range_lower, MAX(CASE WHEN itemid = 50813 THEN ref_range_upper ELSE NULL END) AS lactate_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50820 THEN valuenum ELSE NULL END) AS ph_bg, MAX(CASE WHEN itemid = 50820 THEN ref_range_lower ELSE NULL END) AS ph_bg_ref_range_lower, MAX(CASE WHEN itemid = 50820 THEN ref_range_upper ELSE NULL END) AS ph_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50817 THEN valuenum ELSE NULL END) AS so2_bg, MAX(CASE WHEN itemid = 50817 THEN ref_range_lower ELSE NULL END) AS so2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50817 THEN ref_range_upper ELSE NULL END) AS so2_bg_ref_range_upper \
\
, MAX(CASE WHEN itemid = 50821 THEN valuenum ELSE NULL END) AS po2_bg, MAX(CASE WHEN itemid = 50821 THEN ref_range_lower ELSE NULL END) AS po2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50821 THEN ref_range_upper ELSE NULL END) AS po2_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50818 THEN valuenum ELSE NULL END) AS pco2_bg, MAX(CASE WHEN itemid = 50818 THEN ref_range_lower ELSE NULL END) AS pco2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50818 THEN ref_range_upper ELSE NULL END) AS pco2_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50801 THEN valuenum ELSE NULL END) AS aado2_bg, MAX(CASE WHEN itemid = 50801 THEN ref_range_lower ELSE NULL END) AS aado2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50801 THEN ref_range_upper ELSE NULL END) AS aado2_bg_ref_range_upper\
\
\
--\'e7ouldn't fing ref ranges for aado2_calc , pao2fio2ratio. But these are formed using PO2, PCO2 and fio2. As PO1 and PCO1 already considered above, fio2 is added here, so that ok to omit aado2_calc , pao2fio2ratio.\
\
, MAX(CASE WHEN itemid = 50816 THEN\
CASE\
WHEN valuenum > 20 AND valuenum <= 100 THEN valuenum\
WHEN valuenum > 0.2 AND valuenum <= 1.0 THEN valuenum*100.0\
ELSE NULL END\
ELSE NULL END) AS fio2_bg ,\
MAX(CASE WHEN itemid = 50816 THEN ref_range_lower ELSE NULL END) AS fio2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50816 THEN ref_range_upper ELSE NULL END) AS fio2_bg_ref_range_upper\
\
\
, MAX(CASE WHEN itemid = 50802 THEN valuenum ELSE NULL END) AS baseexcess_bg, MAX(CASE WHEN itemid = 50802 THEN ref_range_lower ELSE NULL END) AS baseexcess_bg_ref_range_lower, MAX(CASE WHEN itemid = 50802 THEN ref_range_upper ELSE NULL END) AS baseexcess_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50803 THEN valuenum ELSE NULL END) AS bicarbonate_bg, MAX(CASE WHEN itemid = 50803 THEN ref_range_lower ELSE NULL END) AS bicarbonate_bg_ref_range_lower, MAX(CASE WHEN itemid = 50803 THEN ref_range_upper ELSE NULL END) AS bicarbonate_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50804 THEN valuenum ELSE NULL END) AS totalco2_bg, MAX(CASE WHEN itemid = 50804 THEN ref_range_lower ELSE NULL END) AS totalco2_bg_ref_range_lower, MAX(CASE WHEN itemid = 50804 THEN ref_range_upper ELSE NULL END) AS totalco2_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50810 THEN valuenum ELSE NULL END) AS hematocrit_bg, MAX(CASE WHEN itemid = 50810 THEN ref_range_lower ELSE NULL END) AS hematocrit_bg_ref_range_lower, MAX(CASE WHEN itemid = 50810 THEN ref_range_upper ELSE NULL END) AS hematocrit_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50811 THEN valuenum ELSE NULL END) AS hemoglobin_bg, MAX(CASE WHEN itemid = 50811 THEN ref_range_lower ELSE NULL END) AS hemoglobin_bg_ref_range_lower, MAX(CASE WHEN itemid = 50811 THEN ref_range_upper ELSE NULL END) AS hemoglobin_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50805 THEN valuenum ELSE NULL END) AS carboxyhemoglobin_bg, MAX(CASE WHEN itemid = 50805 THEN ref_range_lower ELSE NULL END) AS carboxyhemoglobin_bg_ref_range_lower, MAX(CASE WHEN itemid = 50805 THEN ref_range_upper ELSE NULL END) AS carboxyhemoglobin_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50814 THEN valuenum ELSE NULL END) AS methemoglobin_bg, MAX(CASE WHEN itemid = 50814 THEN ref_range_lower ELSE NULL END) AS methemoglobin_bg_ref_range_lower, MAX(CASE WHEN itemid = 50814 THEN ref_range_upper ELSE NULL END) AS methemoglobin_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50825 THEN valuenum ELSE NULL END) AS temperature_bg, MAX(CASE WHEN itemid = 50825 THEN ref_range_lower ELSE NULL END) AS temperature_bg_ref_range_lower, MAX(CASE WHEN itemid = 50825 THEN ref_range_upper ELSE NULL END) AS temperature_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50806 THEN valuenum ELSE NULL END) AS chloride_bg, MAX(CASE WHEN itemid = 50806 THEN ref_range_lower ELSE NULL END) AS chloride_bg_ref_range_lower, MAX(CASE WHEN itemid = 50806 THEN ref_range_upper ELSE NULL END) AS chloride_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50808 THEN valuenum ELSE NULL END) AS calcium_bg, MAX(CASE WHEN itemid = 50808 THEN ref_range_lower ELSE NULL END) AS calcium_bg_ref_range_lower, MAX(CASE WHEN itemid = 50808 THEN ref_range_upper ELSE NULL END) AS calcium_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50809 THEN valuenum ELSE NULL END) AS glucose_bg, MAX(CASE WHEN itemid = 50809 THEN ref_range_lower ELSE NULL END) AS glucose_bg_ref_range_lower, MAX(CASE WHEN itemid = 50809 THEN ref_range_upper ELSE NULL END) AS glucose_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50822 THEN valuenum ELSE NULL END) AS potassium_bg, MAX(CASE WHEN itemid = 50822 THEN ref_range_lower ELSE NULL END) AS potassium_bg_ref_range_lower, MAX(CASE WHEN itemid = 50822 THEN ref_range_upper ELSE NULL END) AS potassium_bg_ref_range_upper\
\
, MAX(CASE WHEN itemid = 50824 THEN valuenum ELSE NULL END) AS sodium_bg, MAX(CASE WHEN itemid = 50824 THEN ref_range_lower ELSE NULL END) AS sodium_bg_ref_range_lower, MAX(CASE WHEN itemid = 50824 THEN ref_range_upper ELSE NULL END) AS sodium_bg_ref_range_upper\
\
from `physionet-data.mimic_hosp.labevents`\
\
WHERE itemid IN\
(\
51221, --hematocrit\
51222, --hemoglobin\
51265, --platelet\
51301, --wbc\
\
50862, --albumin\
50930, --globulin\
50976, --total_protein\
50868, --aniongap\
50882, --bicarbonate\
51006, --bun\
50893, --calcium\
50902, --chloride\
50912, --creatinine\
50931, --glucose\
50983, --sodium\
50971, --potassium\
\
52069, --basophils_abs\
52073, --eosinophils_abs\
51133, --lymphocytes_abs\
52074, --monocytes_abs\
52075, --neutrophils_abs\
51143, --atypical_lymphocytes\
51144, --bands\
52135, --immature_granulocytes\
51251, --metamyelocytes\
51257, --nrbc\
\
51196, --d_dimer\
51214, --fibrinogen\
51297, --thrombin\
51237, --inr\
51274, --pt\
51275, --ptt\
\
50861, --alt\
50863, --alp\
50878, --ast\
50867, --amylase\
50885, --bilirubin_total\
50883, --bilirubin_direct\
50884, --bilirubin_indirect\
50910, --ck_cpk\
50911, --ck_mb\
50927, --ggt\
50954, --ld_ldh\
\
--blood gasses\
\
--52033, -- specimen (='ART' for bg_ART)\
50813, -- lactate_bg\
50820, -- ph_bg\
50817, -- so2_bg\
50821, -- po2_bg\
50818, -- pco2_bg\
50801, -- aado2_bg\
50802, -- baseexcess_bg\
50803, -- bicarbonate_bg\
50804, -- totalco2_bg\
50810, -- hematocrit_bg\
50811, -- hemoglobin_bg\
50805, -- carboxyhemoglobin_bg\
50814, -- methemoglobin_bg\
50825, -- temperature_bg\
50806, -- chloride_bg\
50808, -- calcium_bg\
50809, -- glucose_bg\
50822, -- potassium_bg\
50824, -- sodium_bg\
50816 -- fio2 (added above 'baseexcess_bg')\
\
\
)\
AND valuenum IS NOT NULL -- reason to issue\
\
--AND valuenum > 0 -- lab values can be negative (Ex - baseexcess. therefore, remove this line)\
\
group by specimen_id\
)\
--1.2\
\
, specimen_to_join_item_id_to_name_without_specimen AS -- convert from itemid into a meaningful column full full blood count related parameters in labevents\
(\
SELECT\
MAX(subject_id) as subject_id\
, MAX(hadm_id) as hadm_id\
, specimen_id\
, MAX(charttime) as charttime\
-- convert from itemid into a meaningful column\
\
-- blood gases (vital signs in mimic derived_first24h_bg)\
\
, MAX(CASE WHEN itemid = 52033 THEN value ELSE NULL END) AS specimen -- when this value = 'ART', can use for bg_art\
\
from `physionet-data.mimic_hosp.labevents`\
\
WHERE itemid IN\
(\
\
--blood gasses\
\
52033 -- specimen (='ART' for bg_ART)\
\
)\
--AND valuenum IS NOT NULL -- reason to issue\
-- lab values cannot be 0 and cannot be negative\
--AND valuenum > 0\
\
group by specimen_id\
)\
\
, item_id_to_name_after_joining_specimen AS \
(\
select without_specimen.*, specimen_only.specimen\
from item_id_to_name_without_specimen without_specimen\
left join specimen_to_join_item_id_to_name_without_specimen specimen_only\
on without_specimen.specimen_id = specimen_only.specimen_id\
)\
\
-- 2. Get first 24h vital signs for each icu stay_id\
\
, first24h_vital_signs AS\
(\
SELECT\
min(ie.subject_id) as subject_id\
, min(ie.hadm_id) as hadm_id\
, min(ie.stay_id) as stay_id\
\
--labs \
\
, min(hematocrit) as hematocrit_lab_min\
, max(hematocrit) as hematocrit_lab_max\
, min( hematocrit_ref_range_lower) as hematocrit_lab_ref_range_lower\
, max( hematocrit_ref_range_upper) as hematocrit_lab_ref_range_upper\
\
, min(hemoglobin) as hemoglobin_lab_min\
, max(hemoglobin) as hemoglobin_lab_max\
, min( hemoglobin_ref_range_lower) as hemoglobin_lab_ref_range_lower\
, max( hemoglobin_ref_range_upper) as hemoglobin_lab_ref_range_upper\
\
, min(platelets) as platelets_lab_min\
, max(platelets) as platelets_lab_max\
, min( platelets_ref_range_lower) as platelets_lab_ref_range_lower\
, max( platelets_ref_range_upper) as platelets_lab_ref_range_upper\
\
, min(wbc) as wbc_lab_min\
, max(wbc) as wbc_lab_max\
, min( wbc_ref_range_lower) as wbc_lab_ref_range_lower \
, max( wbc_ref_range_upper) as wbc_lab_ref_range_upper\
\
, min(albumin) as albumin_lab_min\
, max(albumin) as albumin_lab_max\
, min( albumin_ref_range_lower) as albumin_lab_ref_range_lower\
, max( albumin_ref_range_upper) as albumin_lab_ref_range_upper\
\
, min(globulin) as globulin_lab_min\
, max(globulin) as globulin_lab_max\
, min( globulin_ref_range_lower) as globulin_lab_ref_range_lower\
, max( globulin_ref_range_upper) as globulin_lab_ref_range_upper\
\
, min(total_protein) as total_protein_lab_min\
, max(total_protein) as total_protein_lab_max\
, min( total_protein_ref_range_lower) as total_protein_lab_ref_range_lower\
, max( total_protein_ref_range_upper) as total_protein_lab_ref_range_upper\
\
, min(aniongap) as aniongap_lab_min\
, max(aniongap) as aniongap_lab_max\
, min( aniongap_ref_range_lower) as aniongap_lab_ref_range_lower\
, max( aniongap_ref_range_upper) as aniongap_lab_ref_range_upper\
\
, min(bicarbonate) as bicarbonate_lab_min\
, max(bicarbonate) as bicarbonate_lab_max\
, min( bicarbonate_ref_range_lower) as bicarbonate_lab_ref_range_lower\
, max( bicarbonate_ref_range_upper) as bicarbonate_lab_ref_range_upper\
\
, min(bun) as bun_lab_min\
, max(bun) as bun_lab_max\
, min( bun_ref_range_lower) as bun_lab_ref_range_lower\
, max( bun_ref_range_upper) as bun_lab_ref_range_upper\
\
, min(calcium) as calcium_lab_min\
, max(calcium) as calcium_lab_max\
, min( calcium_ref_range_lower) as calcium_lab_ref_range_lower\
, max( calcium_ref_range_upper) as calcium_lab_ref_range_upper\
\
, min(chloride) as chloride_lab_min\
, max(chloride) as chloride_lab_max\
, min( chloride_ref_range_lower) as chloride_lab_ref_range_lower\
, max( chloride_ref_range_upper) as chloride_lab_ref_range_upper\
\
, min(creatinine) as creatinine_lab_min\
, max(creatinine) as creatinine_lab_max\
, min( creatinine_ref_range_lower) as creatinine_lab_ref_range_lower\
, max( creatinine_ref_range_upper) as creatinine_lab_ref_range_upper\
\
, min(glucose) as glucose_lab_min\
, max(glucose) as glucose_lab_max\
, min( glucose_ref_range_lower) as glucose_lab_ref_range_lower\
, max( glucose_ref_range_upper) as glucose_lab_ref_range_upper\
\
, min(sodium) as sodium_lab_min\
, max(sodium) as sodium_lab_max\
, min( sodium_ref_range_lower) as sodium_lab_ref_range_lower\
, max( sodium_ref_range_upper) as sodium_lab_ref_range_upper\
\
, min(potassium) as potassium_lab_min\
, max(potassium) as potassium_lab_max\
, min( potassium_ref_range_lower) as potassium_lab_ref_range_lower\
, max( potassium_ref_range_upper) as potassium_lab_ref_range_upper\
\
, min(abs_basophils) as abs_basophils_lab_min\
, max(abs_basophils) as abs_basophils_lab_max\
, min( abs_basophils_ref_range_lower) as abs_basophils_lab_ref_range_lower\
, max( abs_basophils_ref_range_upper) as abs_basophils_lab_ref_range_upper\
\
, min(abs_eosinophils) as abs_eosinophils_lab_min\
, max(abs_eosinophils) as abs_eosinophils_lab_max\
, min( abs_eosinophils_ref_range_lower) as abs_eosinophils_lab_ref_range_lower\
, max( abs_eosinophils_ref_range_upper) as abs_eosinophils_lab_ref_range_upper\
\
, min(abs_lymphocytes) as abs_lymphocytes_lab_min\
, max(abs_lymphocytes) as abs_lymphocytes_lab_max\
, min( abs_lymphocytes_ref_range_lower) as abs_lymphocytes_lab_ref_range_lower\
, max( abs_lymphocytes_ref_range_upper) as abs_lymphocytes_lab_ref_range_upper\
\
, min(abs_monocytes) as abs_monocytes_lab_min\
, max(abs_monocytes) as abs_monocytes_lab_max\
, min( abs_monocytes_ref_range_lower) as abs_monocytes_lab_ref_range_lower\
, max( abs_monocytes_ref_range_upper) as abs_monocytes_lab_ref_range_upper\
\
, min(abs_neutrophils) as abs_neutrophils_lab_min\
, max(abs_neutrophils) as abs_neutrophils_lab_max\
, min( abs_neutrophils_ref_range_lower) as abs_neutrophils_lab_ref_range_lower\
, max( abs_neutrophils_ref_range_upper) as abs_neutrophils_lab_ref_range_upper\
\
, min(atyps) as atyps_lab_min\
, max(atyps) as atyps_lab_max\
, min( atyps_ref_range_lower) as atyps_lab_ref_range_lower\
, max( atyps_ref_range_upper) as atyps_lab_ref_range_upper\
\
, min(bands) as bands_lab_min\
, max(bands) as bands_lab_max\
, min( bands_ref_range_lower) as bands_lab_ref_range_lower\
, max( bands_ref_range_upper) as bands_lab_ref_range_upper\
\
, min(imm_granulocytes) as imm_granulocytes_lab_min\
, max(imm_granulocytes) as imm_granulocytes_lab_max\
, min( imm_granulocytes_ref_range_lower) as imm_granulocytes_lab_ref_range_lower\
, max( imm_granulocytes_ref_range_upper) as imm_granulocytes_lab_ref_range_upper\
\
, min(metas) as metas_lab_min\
, max(metas) as metas_lab_max\
, min( metas_ref_range_lower) as metas_lab_ref_range_lower\
, max( metas_ref_range_upper) as metas_lab_ref_range_upper\
\
, min(nrbc) as nrbc_lab_min\
, max(nrbc) as nrbc_lab_max\
, min( nrbc_ref_range_lower) as nrbc_lab_ref_range_lower\
, max( nrbc_ref_range_upper) as nrbc_lab_ref_range_upper\
\
, min(d_dimer) as d_dimer_lab_min\
, max(d_dimer) as d_dimer_lab_max\
, min( d_dimer_ref_range_lower) as d_dimer_lab_ref_range_lower\
, max( d_dimer_ref_range_upper) as d_dimer_lab_ref_range_upper\
\
, min(fibrinogen) as fibrinogen_lab_min\
, max(fibrinogen) as fibrinogen_lab_max\
, min( fibrinogen_ref_range_lower) as fibrinogen_lab_ref_range_lower\
, max( fibrinogen_ref_range_upper) as fibrinogen_lab_ref_range_upper\
\
, min(thrombin) as thrombin_lab_min\
, max(thrombin) as thrombin_lab_max\
, min( thrombin_ref_range_lower) as thrombin_lab_ref_range_lower\
, max( thrombin_ref_range_upper) as thrombin_lab_ref_range_upper\
\
, min(inr) as inr_lab_min\
, max(inr) as inr_lab_max\
, min( inr_ref_range_lower) as inr_lab_ref_range_lower\
, max( inr_ref_range_upper) as inr_lab_ref_range_upper\
\
, min(pt) as pt_lab_min\
, max(pt) as pt_lab_max\
, min( pt_ref_range_lower) as pt_lab_ref_range_lower\
, max( pt_ref_range_upper) as pt_lab_ref_range_upper\
\
, min(ptt) as ptt_lab_min\
, max(ptt) as ptt_lab_max\
, min( ptt_ref_range_lower) as ptt_lab_ref_range_lower\
, max( ptt_ref_range_upper) as ptt_lab_ref_range_upper\
\
, min(alt) as alt_lab_min\
, max(alt) as alt_lab_max\
, min( alt_ref_range_lower) as alt_lab_ref_range_lower\
, max( alt_ref_range_upper) as alt_lab_ref_range_upper\
\
, min(alp) as alp_lab_min\
, max(alp) as alp_lab_max\
, min( alp_ref_range_lower) as alp_lab_ref_range_lower\
, max( alp_ref_range_upper) as alp_lab_ref_range_upper\
\
, min(ast) as ast_lab_min\
, max(ast) as ast_lab_max\
, min( ast_ref_range_lower) as ast_lab_ref_range_lower\
, max( ast_ref_range_upper) as ast_lab_ref_range_upper\
\
, min(amylase) as amylase_lab_min\
, max(amylase) as amylase_lab_max\
, min( amylase_ref_range_lower) as amylase_lab_ref_range_lower\
, max( amylase_ref_range_upper) as amylase_lab_ref_range_upper\
\
, min(bilirubin_total) as bilirubin_total_lab_min\
, max(bilirubin_total) as bilirubin_total_lab_max\
, min( bilirubin_total_ref_range_lower) as bilirubin_total_lab_ref_range_lower\
, max( bilirubin_total_ref_range_upper) as bilirubin_total_lab_ref_range_upper\
\
, min(bilirubin_direct) as bilirubin_direct_lab_min\
, max(bilirubin_direct) as bilirubin_direct_lab_max\
, min( bilirubin_direct_ref_range_lower) as bilirubin_direct_lab_ref_range_lower\
, max( bilirubin_direct_ref_range_upper) as bilirubin_direct_lab_ref_range_upper\
\
, min(bilirubin_indirect) as bilirubin_indirect_lab_min\
, max(bilirubin_indirect) as bilirubin_indirect_lab_max\
, min( bilirubin_indirect_ref_range_lower) as bilirubin_indirect_lab_ref_range_lower\
, max( bilirubin_indirect_ref_range_upper) as bilirubin_indirect_lab_ref_range_upper\
\
, min(ck_cpk) as ck_cpk_lab_min\
, max(ck_cpk) as ck_cpk_lab_max\
, min( ck_cpk_ref_range_lower) as ck_cpk_lab_ref_range_lower\
, max( ck_cpk_ref_range_upper) as ck_cpk_lab_ref_range_upper\
\
, min(ck_mb) as ck_mb_lab_min\
, max(ck_mb) as ck_mb_lab_max\
, min( ck_mb_ref_range_lower) as ck_mb_lab_ref_range_lower\
, max( ck_mb_ref_range_upper) as ck_mb_lab_ref_range_upper\
\
, min(ggt) as ggt_lab_min\
, max(ggt) as ggt_lab_max\
, min( ggt_ref_range_lower) as ggt_lab_ref_range_lower\
, max( ggt_ref_range_upper) as ggt_lab_ref_range_upper\
\
, min(ld_ldh) as ld_ldh_lab_min\
, max(ld_ldh) as ld_ldh_lab_max\
, min( ld_ldh_ref_range_lower) as ld_ldh_lab_ref_range_lower\
, max( ld_ldh_ref_range_upper) as ld_ldh_lab_ref_range_upper\
\
-- blood gass\
\
, min(lactate_bg) as lactate_bg_min , max(lactate_bg) as lactate_bg_max , min( lactate_bg_ref_range_lower) as lactate_bg_ref_range_lower , max( lactate_bg_ref_range_upper) as lactate_bg_ref_range_upper\
\
, min(ph_bg) as ph_bg_min , max(ph_bg) as ph_bg_max , min( ph_bg_ref_range_lower) as ph_bg_ref_range_lower , max( ph_bg_ref_range_upper) as ph_bg_ref_range_upper\
\
, min(so2_bg) as so2_bg_min , max(so2_bg) as so2_bg_max , min( so2_bg_ref_range_lower) as so2_bg_ref_range_lower , max( so2_bg_ref_range_upper) as so2_bg_ref_range_upper\
\
, min(po2_bg) as po2_bg_min , max(po2_bg) as po2_bg_max , min( po2_bg_ref_range_lower) as po2_bg_ref_range_lower , max( po2_bg_ref_range_upper) as po2_bg_ref_range_upper\
\
, min(pco2_bg) as pco2_bg_min , max(pco2_bg) as pco2_bg_max , min( pco2_bg_ref_range_lower) as pco2_bg_ref_range_lower , max( pco2_bg_ref_range_upper) as pco2_bg_ref_range_upper\
\
, min(aado2_bg) as aado2_bg_min , max(aado2_bg) as aado2_bg_max , min( aado2_bg_ref_range_lower) as aado2_bg_ref_range_lower , max( aado2_bg_ref_range_upper) as aado2_bg_ref_range_upper\
\
\
, min(fio2_bg) as fio2_bg_min , max(fio2_bg) as fio2_bg_max , min( fio2_bg_ref_range_lower) as fio2_bg_ref_range_lower , max( fio2_bg_ref_range_upper) as fio2_bg_ref_range_upper\
\
\
, min(baseexcess_bg) as baseexcess_bg_min , max(baseexcess_bg) as baseexcess_bg_max , min( baseexcess_bg_ref_range_lower) as baseexcess_bg_ref_range_lower , max( baseexcess_bg_ref_range_upper) as baseexcess_bg_ref_range_upper\
\
, min(bicarbonate_bg) as bicarbonate_bg_min , max(bicarbonate_bg) as bicarbonate_bg_max , min( bicarbonate_bg_ref_range_lower) as bicarbonate_bg_ref_range_lower , max( bicarbonate_bg_ref_range_upper) as bicarbonate_bg_ref_range_upper\
\
, min(totalco2_bg) as totalco2_bg_min , max(totalco2_bg) as totalco2_bg_max , min( totalco2_bg_ref_range_lower) as totalco2_bg_ref_range_lower , max( totalco2_bg_ref_range_upper) as totalco2_bg_ref_range_upper\
\
, min(hematocrit_bg) as hematocrit_bg_min , max(hematocrit_bg) as hematocrit_bg_max , min( hematocrit_bg_ref_range_lower) as hematocrit_bg_ref_range_lower , max( hematocrit_bg_ref_range_upper) as hematocrit_bg_ref_range_upper\
\
, min(hemoglobin_bg) as hemoglobin_bg_min , max(hemoglobin_bg) as hemoglobin_bg_max , min( hemoglobin_bg_ref_range_lower) as hemoglobin_bg_ref_range_lower , max( hemoglobin_bg_ref_range_upper) as hemoglobin_bg_ref_range_upper\
\
, min(carboxyhemoglobin_bg) as carboxyhemoglobin_bg_min , max(carboxyhemoglobin_bg) as carboxyhemoglobin_bg_max , min( carboxyhemoglobin_bg_ref_range_lower) as carboxyhemoglobin_bg_ref_range_lower , max( carboxyhemoglobin_bg_ref_range_upper) as carboxyhemoglobin_bg_ref_range_upper\
\
, min(methemoglobin_bg) as methemoglobin_bg_min , max(methemoglobin_bg) as methemoglobin_bg_max , min( methemoglobin_bg_ref_range_lower) as methemoglobin_bg_ref_range_lower , max( methemoglobin_bg_ref_range_upper) as methemoglobin_bg_ref_range_upper\
\
, min(temperature_bg) as temperature_bg_min , max(temperature_bg) as temperature_bg_max , min( temperature_bg_ref_range_lower) as temperature_bg_ref_range_lower , max( temperature_bg_ref_range_upper) as temperature_bg_ref_range_upper\
\
, min(chloride_bg) as chloride_bg_min , max(chloride_bg) as chloride_bg_max , min( chloride_bg_ref_range_lower) as chloride_bg_ref_range_lower , max( chloride_bg_ref_range_upper) as chloride_bg_ref_range_upper\
\
, min(calcium_bg) as calcium_bg_min , max(calcium_bg) as calcium_bg_max , min( calcium_bg_ref_range_lower) as calcium_bg_ref_range_lower , max( calcium_bg_ref_range_upper) as calcium_bg_ref_range_upper\
\
, min(glucose_bg) as glucose_bg_min , max(glucose_bg) as glucose_bg_max , min( glucose_bg_ref_range_lower) as glucose_bg_ref_range_lower , max( glucose_bg_ref_range_upper) as glucose_bg_ref_range_upper\
\