Open sebbacon opened 4 years ago
Flag for ICU admission (where some support was given, not limited to e.g. just tests):
SELECT
CASE WHEN
IcuAdmissionDateTime IS NOT NULL -- ICU admission date time is present (current hospital)
-- we should check that this will be complete even if transferred in from another hospital's ICU
AND BasicDays_RespiratorySupport+AdvancedDays_RespiratorySupport >= 1 -- at least one day basic / advanced respiratory support
-- there are other "levels of support" fields we can add to this logic
THEN 1 ELSE 0 END,
Date admitted to ICU
CASE WHEN BasicDays_RespiratorySupport+AdvancedDays_RespiratorySupport >= 1 -- at least one day basic / advanced respiratory support THEN MIN(IcuAdmissionDateTime, OriginalIcuAdmissionDate) -- earliest date of current hospital ITU admission and original ITU admission (if transferred in) ELSE NULL END
- Optional logic for ventilation
`Ventilator = 'TRUE'` -- need to know how this field is coded (y/n, 0/1, true/false)
## Death
- the discharge fields in the spreadsheet above (from ICU, from hospital) appear to be applicable if patient is alive, according to the data flows document. There don't appear to be any explicit death fields present in the spreadsheet, but there are 4 fields unidentified which may possibly indicate survival but these can't be interpreted from the spec documents (`yhsurv`, `ausurv` etc).
- Assuming we have all the necessary fields we can use the following logic:
- Flag for death:
```sql
SELECT
CASE WHEN
StatusOnDischargeFromUnit = "D" -- from DISICU? field (D=dead)
OR UltimateStatusOnDischarge = "D" -- from DIS field (D=dead)
-- note this second clause may include patients who survived Covid but
-- went on to die from something else while still in hospital.
-- Not including this could exclude patients who are moved
-- out of ICU to die from Covid (may be unlikely/small numbers).
THEN 1 ELSE 0 END,
We may also want a flag for those who have survived (A
), to distinguish from those still in ICU with as yet unknown outcome (E
)).
Date of death
DateOfDeath -- from DOD field or DOD_TOD datetime field
We can assume this is complete but might be worth checking that this is not missing in the case of patients with a "D" status.
Field name mappings, i.e. column two are the field names in the table in our database
This is based on the Data Flows ICNARC documentation
There is also a Tech Spec which may or may not be of use...
Fields specification