qsofa.sql

-- ------------------------------------------------------------------
-- Title: Quick Sequential Organ Failure Assessment (qSOFA)
-- This query extracts the quick sequential organ failure assessment.
-- This score was a recent revision of SOFA, aiming to detect patients at risk of sepsis.
-- The score is calculated on the first day of each ICU patients' stay - though needn't be.
-- ------------------------------------------------------------------

-- Reference for qSOFA:
--    Singer M, et al. The Third International Consensus Definitions for Sepsis and Septic Shock (Sepsis-3)
--    Seymour CW, et al. Assessment of Clinical Criteria for Sepsis: For the Third International Consensus Definitions for Sepsis and Septic Shock (Sepsis-3)

-- Variables used in qSOFA:
--  GCS, respiratory rate, systolic blood pressure

-- The following views required to run this query:
--  1) gcsfirstday - generated by gcs-first-day.sql
--  2) vitalsfirstday - generated by vitals-first-day.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 scorecomp as
(
select ie.icustay_id
  , v.sysbp_min
  , v.resprate_max
  , gcs.mingcs
FROM `physionet-data.mimiciii_clinical.icustays` ie
left join `physionet-data.mimiciii_clinical.vitals_first_day` v
  on ie.icustay_id = v.icustay_id
left join `physionet-data.mimiciii_clinical.gcs_first_day` gcs
  on ie.icustay_id = gcs.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 sysbp_min is null then null
      when sysbp_min   <= 100 then 1
      else 0 end
    as sysbp_score
  , case
      when mingcs is null then null
      when mingcs   <= 13 then 1
      else 0 end
    as gcs_score
  , case
      when resprate_max is null then null
      when resprate_max   >= 22 then 1
      else 0 end
    as resprate_score
  from scorecomp
)
select ie.subject_id, ie.hadm_id, ie.icustay_id
, coalesce(sysbp_score,0)
 + coalesce(gcs_score,0)
 + coalesce(resprate_score,0)
 as qsofa
, sysbp_score
, gcs_score
, resprate_score
FROM `physionet-data.mimiciii_clinical.icustays` ie
left join scorecalc s
  on ie.icustay_id = s.icustay_id
order by ie.icustay_id;