I got some problem where I can't really wrap my head around.

I have a table with timestamps for ventilation duration which contains the whole duration of different episodes in minutes where the patient was ventilated and some other data a key to with the patient's case ID.

Here is an example of the table:

So as you see one case can have multiple rows in this table. Now in another table I have the departments and stations where the patient / case was during their stay in hospital (from admission to discharge) - also with timestamps:

Now I'd like to build a query to sum up the ventilation duration on specific stations. As you can see I don't have a station reference in the first table, so I have to compare the timestamps to get the time when we was on this specific station (for example - like here - with ID 123).

I think I get the logic that the beginning of the ventilation must be more than or equal to the beginning of the station's visit and the end has to be less than or equal to the end of the station's visit, but I'm not sure how to deal with those multiple stays (e.g. when re-admissioned).

I came up with the following query which seems to work but the numbers are a bit on the lower side in my opinion:

@set start_date = '01.01.2018 00:00:00'
@set end_date = '31.08.2021 23:59:59'
@set format = 'DD.MM.YYYY HH24:MI:SS'

SELECT 
    "Year",
    SUM("Ventilation Duration") AS "Sum"
FROM 
    (SELECT 
        f.CASEID AS "FallID",
        SUM(DECODE(fb.DURATION, 20 , ROUND(fb.DURATION / 60, 0), 21, fb.DURATION)) AS "Ventilation Duration",
        TO_CHAR(fa.BEGIN, 'DD.MM.YYYY HH24:MI') AS "Begin Station Stay",
        TO_CHAR(fb.BEGIN, 'DD.MM.YYYY HH24:MI') AS "Begin Ventilation",
        TO_CHAR(fb.END, 'DD.MM.YYYY HH24:MI') AS "End Ventilation",
        TO_CHAR(fa.END, 'DD.MM.YYYY HH24:MI') AS "End Station Stay",
        oe.STATION_NAME AS "Station",
        TO_CHAR(f.DISCHARGE_DATE, 'YYYY') AS "YEAR"
    FROM 
        CASE_VENTLATION fb
    INNER JOIN 
        CASE_STAY fa ON fa.CASEID = fb.CASEID
    INNER JOIN 
        CASE f ON f.CASEID = fb.CASEID 
    INNER JOIN 
        STATION oe ON oe.STATIONID = fa.STATIONID
    WHERE 
        f.STORNO_DATUM IS NULL 
        AND f.DISCHARGE_DATE BETWEEN TO_DATE(:start_date, :format) AND TO_DATE(:end_date, :format)
        AND fa.STATIONID = 10097
        AND f.CASEMARK IN (38140, 38142)
        AND fb.BEGIN >=(SELECT MIN(fa.BEGIN)FROM CASE_STAY fa2 WHERE fa2.CASEID = fb.CASEID AND fa2.STATION_ID = fa.STATION_ID)
        AND fb.END <= SELECT MAX(fa.ENDE)FROM CASE_STAY fa2 WHERE fa2.CASEID = fb.CASEID AND  fa2.STATION_ID = fa.STATION_ID)
    GROUP BY
        ...
    )
GROUP BY "Year"
ORDER BY "Year"

Would you consider this als logically correct or can I write a better WHERE-Condition - especially with the MIN and MAX functions.

Don't mind the aliases, I translated some field names from German so it's easier to understand to what fields in the example they refer.

EDIT: As requested, this is my current result:

Problem is, as I said, it's a bit on the lower side and I guess I missed some due to those timestamp overlaps :/.

Any help is greatly appreciated!

Thanks!

CodePudding user response：

To be honest, I am not completely sure whether I got the task correctly but it seems to be interesting, so I'll try. Sorry if I'm wrong.

If all you need is to calculate amount of minutes patients were ventilated on each station, here is what I have:

select st.station_id, 
      v.caseid,
      sum((least(v.end_date, st.end_date) - greatest(v.begin_date, st.begin_date)) * 24 * 60) vent_duration_mins
 from vent v
 join dep_stat st
   on v.caseid = st.caseid
  and v.begin_date < st.end_date 
  and v.end_date > st.begin_date
group by st.station_id,          
         v.caseid                

This case assumes all date columns are of date type, not timestamp

db_fiddle