 -- icd-9 diagnostic codes are our best source for comorbidity information
 -- unfortunately, they are technically a-causal
 -- however, this shouldn't matter too much for the SAPS II comorbidities
-, icd AS
-(
-    select 
-        hadm_id
-        , seq_num
-        , CASE WHEN icd_version = 10 THEN
-                cast(icd_code as char(5))
-        ELSE NULL END AS icd10_code
-        , CASE WHEN icd_version = 9 THEN
-                cast(icd_code as char(5))
-        ELSE NULL END AS icd9_code
-    from `physionet-data.mimic_hosp.diagnoses_icd`
-)
 , comorb as
 (
 select hadm_id

    co.stay_id
  , bg.charttime
  , pao2fio2ratio AS PaO2FiO2
  , case when vd.subject_id is not null then 1 else 0 end as vent
  , case when cp.subject_id is not null then 1 else 0 end as cpap
  from co
  LEFT JOIN `physionet-data.mimic_derived.bg` bg

    AND bg.charttime > co.starttime
    AND bg.charttime <= co.endtime
  left join `physionet-data.mimic_derived.ventilation` vd
    on co.stay_id = vd.stay_id
    and bg.charttime > vd.starttime
    and bg.charttime <= vd.endtime
    and vd.ventilation_status = 'InvasiveVent'
