Mechanical ventilation start and end times

[jayaramanp]

Hello, I am trying to figure out how to capture mechanical ventilation start and end times. I understand that the closest would be the: https://github.com/nus-mornin-lab/oxygenation_kc/blob/master/data-extraction/eICU/eicu_oxygen_therapy.sql

However, that specific codeblurb is giving me errors in the postgres sql space (possibly due to something im doing wrong).

Does someone have a working postgres query to get ventilation start and end times that they have successfully managed to get for patents who were mechanically ventilated in eICU?

I wrote something that pulls in the "prior vent start and end offsets" but that may only apply to patients who were mechanically ventilated more than once (but this code is able to ignore the artifacts as well.. ventstartoffsets that start shortly earlier and never end.. or duplicated records). I am still unable to get the correct time range for patients who were mechanically ventilated the first time.

This is what I came up with for patients who were ventilated more than once.

with patient_resp AS (
    select p.patientunitstayid,
           p.patienthealthsystemstayid,
           p.unitvisitnumber,
           r.currenthistoryseqnum,
           p.unitadmittime24,
           p.unitdischargetime24,
           p.unitdischargeoffset,
           p.uniquepid,
           r.respcareid,
           r.respcarestatusoffset,
           r.ventstartoffset,
           r.ventendoffset,
           r.priorventstartoffset,
           r. priorventendoffset
    from eicu.patient p
        inner join eicu.respiratorycare r
        on
            p.patientunitstayid = r.patientunitstayid
    --group by uniquepid, p.patientunitstayid, patienthealthsystemstayid, currenthistoryseqnum
    order by uniquepid, patientunitstayid, patienthealthsystemstayid, currenthistoryseqnum
)
, patient_resp_vent_info AS (
    select presp.uniquepid,
       presp.patientunitstayid,
       presp.patienthealthsystemstayid,
       presp.unitvisitnumber,
       presp.currenthistoryseqnum,
       presp.unitadmittime24,
       presp.unitdischargetime24,
       presp.unitdischargeoffset,
       presp.ventstartoffset,
       presp.priorventendoffset,
       LEAD(presp.priorventendoffset, 1)
       OVER (partition by presp.patientunitstayid
           order by
               presp.uniquepid, presp.patientunitstayid, presp.patienthealthsystemstayid, presp.currenthistoryseqnum) as possible_ventend_offset,
       presp.priorventstartoffset,
       LEAD(presp.priorventstartoffset, 1)
       OVER (partition by presp.patientunitstayid
           order by
               presp.uniquepid, presp.patientunitstayid, presp.patienthealthsystemstayid, presp.currenthistoryseqnum) as known_ventstart_offset,
       LEAD(presp.patientunitstayid, 1)
       OVER (partition by presp.patientunitstayid
           order by
               presp.uniquepid, presp.patientunitstayid, presp.patienthealthsystemstayid, presp.currenthistoryseqnum) as patientunitstayid_withpriorventinfo
    from patient_resp presp
    order by presp.uniquepid, presp.patientunitstayid, presp.patienthealthsystemstayid, presp.currenthistoryseqnum
)
select * from patient_resp_vent_info prvi