 -- 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

    WHEN icd_version = 10 AND SUBSTR(icd_code, 1, 3) BETWEEN 'C77' AND 'C79' THEN 1
    WHEN icd_version = 10 AND SUBSTR(icd_code, 1, 4) = 'C800' THEN 1
    ELSE 0 END) as mets      /* Metastatic cancer */
  from icd
  group by hadm_id
)


, gcs AS
(
    select co.stay_id
    , MIN(gcs.gcs) AS mingcs
    FROM co
    left join `physionet-data.mimic_derived.gcs` gcs
    ON co.stay_id = gcs.stay_id
