sirs.sql

-- THIS SCRIPT IS AUTOMATICALLY GENERATED. DO NOT EDIT IT DIRECTLY.
DROP TABLE IF EXISTS mimiciv_derived.sirs; CREATE TABLE mimiciv_derived.sirs AS
/* ------------------------------------------------------------------ */ /* 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 mimiciii_derived.blood_gas_first_day_arterial AS bg
  WHERE
    specimen_pred = 'ART'
  GROUP BY
    bg.icustay_id
), scorecomp /* Aggregate the components for the score */ 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 mimiciii.icustays AS ie
  LEFT JOIN bg
    ON ie.icustay_id = bg.icustay_id
  LEFT JOIN mimiciii_derived.vitals_first_day AS v
    ON ie.icustay_id = v.icustay_id
  LEFT JOIN mimiciii_derived.labs_first_day AS 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 neutrophils (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 component scores to get SIRS */ /* 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 mimiciii.icustays AS ie
LEFT JOIN scorecalc AS s
  ON ie.icustay_id = s.icustay_id
ORDER BY
  ie.icustay_id NULLS FIRST