I’ve got a rather unusual question about some database query with oracle.
I got asked if it’s possible to get the number of cases where the patient got a resumption on the same station they were discharged from within 48 / 72 hours.
Consider the following example:
| Case | Station | From | To |
|---|---|---|---|
| 1 | Stat_1 | 2020-01-03 20:10:00 | 2020-01-04 17:40:00 |
| 1 | Stat_2 | 2020-01-04 17:40:00 | 2020-01-05 09:35:00 |
| 1 | Stat_1 | 2020-01-05 09:35:00 | 2020-01-10 12:33:00 |
In this example, I’d have to check the difference between the last discharge time from station one and the first admission time when he’s again registered at station 1. This should then count as one readmission.
I’ve tried some stuff with LAG and LEAD, but you can’t use them in the WHERE-Clause, so that’s not too useful I guess.
LAG (o.OEBENEID, 1, 0) OVER (ORDER BY vfs.GUELTIG_BIS) AS Prev_Stat, LEAD (o.OEBENEID, 1, 0) OVER (ORDER BY vfs.GUELTIG_BIS) AS Next_Stat, LAG (vfs.GUELTIG_BIS, 1) OVER (ORDER BY vfs.GUELTIG_BIS) AS End_Prev_Stat, LEAD (vfs.GUELTIG_AB, 1) OVER (ORDER BY vfs.GUELTIG_AB) AS Begin_Next_Stat
I am able to get the old values, but I can’t do something like calculate the difference between those two dates.
Is this even possible to achieve? I can’t really wrap my head around how to do it with SQL.
Thanks in advance!
Advertisement
Answer
You need a partition by clause to retrieve the previous discharge date of the same user in the same station. Then, you can filter in an outer query:
select count(*) as cnt
from (
select case_no, station, dt_from, dt_to
lag(dt_to) over(partition by case_no, station order by dt_from) as lag_dt_to
from mytable t
) t
where dt_from < lag_dt_to + 2
This counts how many rows have a gap of less than 2 days with the previous discharge date of the same user in the same station.
This assumes that your are string your dates as dates. If you have timestamps instead, you need interval arithmetics, so:
where dt_from < lag_dt_to + interval '2' day
Note that case, from and to are reserverd words in Oracle: I used alternative names in the query.