3

In the MIMIC-III database, I wonder whether there is an easy way to obtain the list of ICUSTAY_IDs that happened in a given HADM_ID.

enter image description here

1

In MIMIC 2V30, to get to the list of ICUSTAY_IDs that happened in a given HADM_ID, you can use:

SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM MIMIC2V30.ADMISSIONS t1
INNER JOIN MIMIC2V30.ICUSTAYEVENTS t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.ADMIT_DT <= t2.INTIME
AND t1.DISCH_DT >= t2.OUTTIME
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

or maybe to account for the fact that ICUSTAYEVENTS.OUTTIME is often after ADMISSIONS.DISCH_DT:

SELECT t1.HADM_ID, COUNT(DISTINCT(t2.ICUSTAY_ID)) cnt
FROM MIMIC2V30.ADMISSIONS t1
INNER JOIN MIMIC2V30.ICUSTAYEVENTS t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.ADMIT_DT <= t2.INTIME
AND CAST(t2.OUTTIME AS DATE)- CAST(t1.DISCH_DT AS DATE) < 1
GROUP BY t1.HADM_ID
ORDER BY cnt DESC;

In MIMIC-III, it's much easier:

-- returns 61532 rows
SELECT HADM_ID, ICUSTAY_ID FROM  mimiciii.icustays 
ORDER BY HADM_ID, ICUSTAY_ID ASC;

It can still use the same strategy as the one used a for MIMIC2V30, but it will yield a less complete list:

-- returns 49185 rows
SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM mimiciii.admissions t1
INNER JOIN mimiciii.icustays t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.admittime <= t2.intime
AND t1.dischtime >= t2.outtime
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

-- returns 60237 rows
SELECT t1.HADM_ID, t2.ICUSTAY_ID
FROM mimiciii.admissions t1
INNER JOIN mimiciii.icustays t2
ON t1.SUBJECT_ID = t2.SUBJECT_ID
-- filter for those time ranges that overlap
AND t1.admittime <= t2.intime
AND CAST(t2.OUTTIME AS DATE)- CAST(t1.DISCHTime AS DATE) < 1
ORDER BY t1.HADM_ID, t2.ICUSTAY_ID ASC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.