Creating a count of items that fits a criteria at each snapshot in time

Question

I'm trying to create a postgres query that gives me a snapshot of the number of open cases at a point in time. I have a table with a list of timestamps, which will be the snapshots in time that I will be using. The second table is a list of cases, with a CASE_OPEN_TIME and a CASE_CLOSED_TIME. Eventually, I am trying to create a table that has a column with the snapshot timestamps, and the number of cases that are still open at that timestamp. Appreciate your help and advice on how I can solve this problem. Thanks in advance :)

SNAPSHOT
2022-07-10 10:00:00
2022-07-09 10:00:00
2022-07-08 10:00:00
2022-07-07 10:00:00
2022-07-06 10:00:00
2022-07-05 10:00:00

CASE_ID	CASE_OPEN_TIME	CASE_CLOSED_TIME
1	2022-07-05 04:00:00	2022-07-05 12:00:00
2	2022-07-06 09:00:00	2022-07-08 12:00:00
3	2022-07-08 09:00:00	2022-07-08 13:00:00
4	2022-07-10 08:00:00	2022-07-10 09:00:00

Final Output:

SNAPSHOT	COUNT_OPEN_CASES
2022-07-10 10:00:00	0
2022-07-09 10:00:00	0
2022-07-08 10:00:00	2
2022-07-07 10:00:00	1
2022-07-06 10:00:00	1
2022-07-05 10:00:00	1

Ftisiot Ftisiot · Accepted Answer · 2022-07-28T14:44:23

if your snapshot table is called SNAPSHOT and the cases CASES isn't the result you want

SELECT
    SNAPSHOT.SNAPSHOT_TIME,
    COUNT(*) NR_CASES
FROM
    SNAPSHOT LEFT OUTER JOIN CASES ON SNAPSHOT.SNAPSHOT_TIME >= CASE_OPEN_TIME AND SNAPSHOT.SNAPSHOT_TIME <= CASE_CLOSSED_TIME
GROUP BY
    SNAPSHOT.SNAPSHOT_TIME

the left outer join would give you the snapshot even if there are no cases

Creating a count of items that fits a criteria at each snapshot in time

1 Answers