Identify all blood draw at bedside with their time for each ICU patients

[RolaAM]

Hi,

I hope someone can help me! I am looking to retrieve and identify, for each patient in the ICU, the total number of blood draw that was performed and the timing at which it was conducted.
I am currently using the d_labitems and labevents tables in mimiciv_hosp to get all the tests that were related to fluid='BLOOD' and grouping by specimen_id (this is a new id that defines tests that were done on the same sample suggesting it is related to the blood sampling at bed side). But then, when I group by charttime, I don't get the same results. It looks like you can have multiple specimen_id related to same blood sample at bedside.

I am looking to have a table with the columns that look like this for each subject_id that was in the ICU during his length of stay at the ICU ward :

subject_id Time of Blood draw 1 Time of Blood draw 2 Time of Blood draw 3 ... Time of Blood draw N

where N is the total number of blood draw from the patient.
Am I looking at the right tables?

Thank you in advance,

Example for subject_id = 12963637

SELECT le.subject_id, COUNT(DISTINCT le.specimen_id) AS specimen_count
FROM physionet-data.mimiciv_hosp.labevents AS le
JOIN physionet-data.mimiciv_hosp.d_labitems AS dl ON le.itemid = dl.itemid
WHERE dl.fluid = 'Blood'AND subject_id=12963637
GROUP BY le.subject_id
HAVING specimen_count >= 2
ORDER BY specimen_count ASC;

outputs count = 6 different specimen_id

but when I do for the same subject_id

SELECT le.subject_id, COUNT(DISTINCT le.charttime) AS charttime_count
FROM physionet-data.mimiciv_hosp.labevents AS le
JOIN physionet-data.mimiciv_hosp.d_labitems AS dl ON le.itemid = dl.itemid
WHERE dl.fluid = 'Blood'AND subject_id=12963637
GROUP BY le.subject_id
HAVING charttime_count >= 2
ORDER BY charttime_count ASC;

this outputs count = 2 different charttime

In this case, should this suggest that the subject_id = 12963637 had only two blood draw at bedside because there was only two different charttime in labevents?