aline_cohort.sql

-- This query defines the cohort used for the ALINE study.

-- Inclusion criteria:
--  adult patients
--  In ICU for at least 24 hours
--  First ICU admission
--  mechanical ventilation within the first 12 hours
--  medical or surgical ICU admission

-- Exclusion criteria:
--  **Angus sepsis
--  **On vasopressors (?is this different than on dobutamine)
--  IAC placed before admission
--  CSRU patients

-- **These exclusion criteria are applied in the data.sql file.

-- This query also extracts demographics, and necessary preliminary flags needed
-- for data extraction. For example, since all data is extracted before
-- ventilation, we need to extract start times of ventilation


-- This query requires the following tables:
--  ventdurations - extracted by mimic-code/concepts/durations/ventilation-durations.sql


DROP MATERIALIZED VIEW IF EXISTS ALINE_COHORT_ALL CASCADE;
CREATE MATERIALIZED VIEW ALINE_COHORT_ALL as

-- get start time of arterial line
-- Definition of arterial line insertion:
--  First measurement of invasive blood pressure
with a as
(
  select icustay_id
  , min(charttime) as starttime_aline
  from chartevents
  where icustay_id is not null
  and valuenum is not null
  and itemid in
  (
    51, --	Arterial BP [Systolic]
    6701, --	Arterial BP #2 [Systolic]
    220050, --	Arterial Blood Pressure systolic

    8368, --	Arterial BP [Diastolic]
    8555, --	Arterial BP #2 [Diastolic]
    220051, --	Arterial Blood Pressure diastolic

    52, --"Arterial BP Mean"
    6702, --	Arterial BP Mean #2
    220052, --"Arterial Blood Pressure mean"
    225312 --"ART BP mean"
  )
  group by icustay_id
)
-- get intime/outtime from vitals rather than administrative data
, co_intime as
(
  select ie.icustay_id, min(charttime) as intime, max(charttime) as outtime
  from icustays ie
  left join chartevents ce
    on ie.icustay_id = ce.icustay_id
    and ce.charttime between ie.intime - interval '12' hour and ie.outtime + interval '12' hour
    and ce.itemid in (211, 220045)
  group by ie.icustay_id
)
-- first time ventilation was started
-- last time ventilation was stopped
, ve as
(
  select icustay_id
    , sum(extract(epoch from endtime-starttime))/24.0/60.0/60.0 as vent_day
    , min(starttime) as starttime_first
    , max(endtime) as endtime_last
  from ventdurations vd
  group by icustay_id
)
, serv as
(
    select ie.icustay_id, se.curr_service
    , ROW_NUMBER() over (partition by ie.icustay_id order by se.transfertime DESC) as rn
    from icustays ie
    inner join services se
      on ie.hadm_id = se.hadm_id
      and se.transfertime < ie.intime + interval '2' hour
)
-- cohort view - used to define other concepts
, co as
(
  select
    ie.subject_id, ie.hadm_id, ie.icustay_id
    , co.intime
    , to_char(co.intime, 'day') as day_icu_intime
    , extract(dow from co.intime) as day_icu_intime_num
    , extract(hour from co.intime) as hour_icu_intime
    , co.outtime

    , ROW_NUMBER() over (partition by ie.subject_id order by adm.admittime, co.intime) as stay_num
    , extract(epoch from (co.intime - pat.dob))/365.242/24.0/60.0/60.0 as age
    , pat.gender
    , case when pat.gender = 'M' then 1 else 0 end as gender_num
    , vf.vaso_flag
    , sep.angus
    -- service

    -- collapse ethnicity into fixed categories

    -- time of a-line
    , a.starttime_aline
    , case when a.starttime_aline is not null then 1 else 0 end as aline_flag
    , extract(epoch from (a.starttime_aline - co.intime))/24.0/60.0/60.0 as aline_time_day
    , case
        when a.starttime_aline is not null
         and a.starttime_aline <= co.intime
          then 1
        else 0
      end as initial_aline_flag

    -- ventilation
    , case when ve.icustay_id is not null then 1 else 0 end as vent_flag
    , case when ve.starttime_first < co.intime + interval '12' hour then 1 else 0 end as vent_1st_12hr
    , case when ve.starttime_first < co.intime + interval '24' hour then 1 else 0 end as vent_1st_24hr

    -- binary flag: were they ventilated before a-line insertion?
    , case
        -- if they were never given an aline, this is a non-sensical question
        when a.starttime_aline is null then null
        -- aline given for sure after ventilation
        when a.starttime_aline > co.intime + interval '1' hour and ve.starttime_first<=a.starttime_aline then 1
        -- aline given for sure after ventilation
        when a.starttime_aline > co.intime + interval '1' hour and ve.starttime_first>a.starttime_aline then 0
        else NULL
      end as vent_b4_aline

    -- number of days on a ventilator
    , ve.vent_day

    -- number of days free of ventilator after *last* extubation
    , extract(epoch from (ie.outtime - ve.endtime_last))/24.0/60.0/60.0 as vent_free_day

    -- number of days *not* on a ventilator
    , extract(epoch from (ie.outtime - co.intime))/24.0/60.0/60.0 - vent_day as vent_off_day


    , ve.starttime_first as vent_starttime
    , ve.endtime_last as vent_endtime

    -- cohort flags // demographics
    , extract(epoch from (ie.outtime - co.intime))/24.0/60.0/60.0 as icu_los_day
    , extract(epoch from (adm.dischtime - adm.admittime))/24.0/60.0/60.0 as hospital_los_day

    -- will be used to exclude patients in CSRU
    -- also only include those in CMED or SURG
    , s.curr_service as service_unit
    , case when s.curr_service like '%SURG' or s.curr_service like '%ORTHO%' then 1
          when s.curr_service = 'CMED' then 2
          when s.curr_service in ('CSURG','VSURG','TSURG') then 3
          else 0
        end
      as service_num

    -- outcome
    , case when adm.deathtime is not null then 1 else 0 end as hosp_exp_flag
    , case when adm.deathtime <= ie.outtime then 1 else 0 end as icu_exp_flag
    , case when pat.dod <= (co.intime + interval '28' day) then 1 else 0 end as day_28_flag
    , extract(epoch from (pat.dod - adm.admittime))/24.0/60.0/60.0 as mort_day

    , case when pat.dod is null
        then 150 -- patient deaths are censored 150 days after admission
        else extract(epoch from (pat.dod - adm.admittime))/24.0/60.0/60.0
      end as mort_day_censored
    , case when pat.dod is null then 1 else 0 end as censor_flag

  from co_intime co
  inner join icustays ie
    on co.icustay_id = ie.icustay_id
  inner join admissions adm
    on ie.hadm_id = adm.hadm_id
  inner join patients pat
    on ie.subject_id = pat.subject_id
  left join a
    on ie.icustay_id = a.icustay_id
  left join ve
    on ie.icustay_id = ve.icustay_id
  left join serv s
    on ie.icustay_id = s.icustay_id
    and s.rn = 1
  left join aline_vaso_flag vf
    on ie.icustay_id = vf.icustay_id
  left join angus_sepsis sep
    on ie.hadm_id = sep.hadm_id
  where co.intime > (pat.dob + interval '16' year) -- only adults
)
select
  co.*
  , case when stay_num > 1 then 1 else 0 end as exclusion_readmission -- first ICU stay
  , case when icu_los_day < 1 then 1 else 0 end exclusion_shortstay -- one day in the ICU
  , case when vaso_flag = 1 then 1 else 0 end as exclusion_vasopressors
  , case when angus = 1 then 1 else 0 end as exclusion_septic
  , case when initial_aline_flag = 1 then 1 else 0 end exclusion_aline_before_admission -- aline must be placed later than admission
  -- exclusion: IAC placement was performed prior to endotracheal intubation and initiation of mechanical ventilation
  -- we do not apply this criteria since it's unclear if this was actually done in the original aline paper
  -- , case when vent_b4_aline = 0 then 1 else 0 end as exclusion_aline_before_vent
  , case when vent_starttime is null or vent_starttime > intime + interval '24' hour then 1 else 0 end exclusion_not_ventilated_first24hr -- were ventilated
  -- above also requires ventilated within first 24 hours
  , case when service_unit in
  (
    -- we need to approximate CCU and CSRU using hospital service
    -- paper only says CSRU but the code did both CCU/CSRU
    -- this is the best guess
    'CMED','CSURG','VSURG','TSURG' -- cardiac/vascular/thoracic surgery
  ) then 1 else 0 end as exclusion_service_surgical
  -- "medical or surgical ICU admission"

from co
order by icustay_id;


CREATE MATERIALIZED VIEW ALINE_COHORT AS
select
  co.*
from ALINE_COHORT_ALL co
where exclusion_readmission = 0 -- first ICU stay
and exclusion_shortstay = 0 -- one day in the ICU
and exclusion_vasopressors = 0
and exclusion_septic = 0
and exclusion_aline_before_admission = 0 -- aline placed later than admission
-- and exclusion_aline_before_vent = 0
and exclusion_not_ventilated_first24hr = 0 -- were ventilated within first 24 hours
and exclusion_service_surgical = 0;