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

[ZBZFirst]

the respiratoryCharting.csv is a data dump. It was never cleaned or processed for analysis.

Each hospitalid has a different way of entering respiratory therapy information. I am looking at writing a paper on this due to industry lack of standardization (i am an RT of 15 years with experience playing how the columns are named for patient information input, i also taught the classroom on charting procedures).

In addition, once you pivot the table to find the columns that you want, you will quickly find that the problem is far more painful then you would hope. Many hospitals allow string entry for their values. So you will find mostly string inputs for values that should be numeric.

So you will need to clean the data by removing all the "@" and "/" and "[]" and "()" and abbreviations.

To make it simpler, Filter to one hospitalid, then run the respiratoryCare.csv based on one hospitalid. You may be able to get something out of it then since the charting will be standard to that one hospitalid.