MIMIC2V26.ICUSTAY_DAYS
DDL scriptColumns
| Name | Type | Nullable | Insertable | Updatable | Deletable | Comment |
|---|
| ICUSTAY_ID | NUMBER(7) | Y | YES | YES | YES | Unique icustay identifier |
| SUBJECT_ID | NUMBER(7) | Y | YES | YES | YES | Unique subject identifier |
| SEQ | NUMBER(7) | Y | YES | YES | YES | Day number of the ICU stay |
| BEGINTIME | TIMESTAMP(6) WITH TIME ZONE | Y | NO | NO | NO | Start time of the ICU stay day |
| ENDTIME | TIMESTAMP(6) WITH TIME ZONE | Y | NO | NO | NO | End time of the ICU stay day. |
| FIRST_DAY_FLG | CHAR(1) | Y | YES | YES | YES | The first day of the ICU stay |
| LAST_DAY_FLG | CHAR(1) | Y | YES | YES | YES | The last day of the ICU stay |
Query:
Note: Syntax highlighting off
WITH Days AS (SELECT
subject_id,
icustay_id,
icustay_day seq,
intime begintime,
outtime endtime
FROM mimic2v26.icustayevents
MODEL RETURN UPDATED ROWS
PARTITION BY (subject_id,icustay_id)
DIMENSION BY (0 icustay_day)
MEASURES (
intime,
outtime
)
RULES ITERATE (1000)
UNTIL (ITERATION_NUMBER >= EXTRACT (DAY FROM outtime[0] - intime[0]))
(
intime[ITERATION_NUMBER + 1] = intime[0] + NUMTODSINTERVAL(ITERATION_NUMBER, 'DAY'),
-- Make sure we stay within the time bounds of the ICU stay
outtime[ITERATION_NUMBER + 1] =
case
when (intime[0] + NUMTODSINTERVAL(ITERATION_NUMBER + 1, 'DAY') > outtime[0])
then outtime[0]
else intime[0] + NUMTODSINTERVAL(ITERATION_NUMBER + 1, 'DAY') - INTERVAL '1' SECOND
end
)
order by subject_id, icustay_id, intime
)
SELECT
CAST(icustay_id as number(7,0)) as icustay_id,
CAST(subject_id as number(7,0)) as subject_id,
CAST(seq as number(7,0)) as seq,
begintime,
endtime,
CASE
WHEN ( seq = first_value(seq)
OVER (PARTITION BY icustay_id
ORDER BY seq) )
THEN 'Y'
ELSE 'N'
END as first_day_flg,
CASE
WHEN ( seq = first_value(seq)
OVER (PARTITION BY icustay_id
ORDER BY seq DESC) )
THEN 'Y'
ELSE 'N'
END as last_day_flg
FROM Days
--WHERE subject_id < 10