sirs.sql

-- ------------------------------------------------------------------
-- Title: Systemic inflammatory response syndrome (SIRS) criteria
-- This query extracts the Systemic inflammatory response syndrome (SIRS) criteria
-- The criteria quantify the level of inflammatory response of the body
-- The score is calculated on the first day of each ICU patients' stay.
-- ------------------------------------------------------------------

-- Reference for SIRS:
--    American College of Chest Physicians/Society of Critical Care Medicine Consensus Conference:
--    definitions for sepsis and organ failure and guidelines for the use of innovative therapies in sepsis"
--    Crit. Care Med. 20 (6): 864–74. 1992.
--    doi:10.1097/00003246-199206000-00025. PMID 1597042.

-- Variables used in SIRS:
--  Body temperature (min and max)
--  Heart rate (max)
--  Respiratory rate (max)
--  PaCO2 (min)
--  White blood cell count (min and max)
--  the presence of greater than 10% immature neutrophils (band forms)

-- The following views required to run this query:
--  1) vitals_first_day - generated by vitals-first-day.sql
--  2) labs_first_day - generated by labs-first-day.sql
--  3) blood_gas_first_day_arterial - generated by blood-gas-first-day-arterial.sql

-- Note:
--  The score is calculated for *all* ICU patients, with the assumption that the user will subselect appropriate ICUSTAY_IDs.
--  For example, the score is calculated for neonates, but it is likely inappropriate to actually use the score values for these patients.

with bg as
(
  -- join blood gas to ventilation durations to determine if patient was vent
  select bg.icustay_id
  , min(pco2) as paco2_min
  from `physionet-data.mimiciii_derived.blood_gas_first_day_arterial` bg
  where specimen_pred = 'ART'
  group by bg.icustay_id
)
-- Aggregate the components for the score
, scorecomp as
(
select ie.icustay_id
  , v.tempc_min
  , v.tempc_max
  , v.heartrate_max
  , v.resprate_max
  , bg.paco2_min
  , l.wbc_min
  , l.wbc_max
  , l.bands_max
FROM `physionet-data.mimiciii_clinical.icustays` ie
left join bg
 on ie.icustay_id = bg.icustay_id
left join `physionet-data.mimiciii_derived.vitals_first_day` v
  on ie.icustay_id = v.icustay_id
left join `physionet-data.mimiciii_derived.labs_first_day` l
  on ie.icustay_id = l.icustay_id
)
, scorecalc as
(
  -- Calculate the final score
  -- note that if the underlying data is missing, the component is null
  -- eventually these are treated as 0 (normal), but knowing when data is missing is useful for debugging
  select icustay_id

  , case
      when tempc_min < 36.0 then 1
      when tempc_max > 38.0 then 1
      when tempc_min is null then null
      else 0
    end as temp_score


  , case
      when heartrate_max > 90.0  then 1
      when heartrate_max is null then null
      else 0
    end as heartrate_score

  , case
      when resprate_max > 20.0  then 1
      when paco2_min < 32.0  then 1
      when coalesce(resprate_max, paco2_min) is null then null
      else 0
    end as resp_score

  , case
      when wbc_min <  4.0  then 1
      when wbc_max > 12.0  then 1
      when bands_max > 10 then 1-- > 10% immature neurophils (band forms)
      when coalesce(wbc_min, bands_max) is null then null
      else 0
    end as wbc_score

  from scorecomp
)
select
  ie.subject_id, ie.hadm_id, ie.icustay_id
  -- Combine all the scores to get SOFA
  -- Impute 0 if the score is missing
  , coalesce(temp_score,0)
  + coalesce(heartrate_score,0)
  + coalesce(resp_score,0)
  + coalesce(wbc_score,0)
    as sirs
  , temp_score, heartrate_score, resp_score, wbc_score
FROM `physionet-data.mimiciii_clinical.icustays` ie
left join scorecalc s
  on ie.icustay_id = s.icustay_id
order by ie.icustay_id;