I have a table which contains timestamps (called triggers) per business day (CTRL_DT). I need to process data for a given business date by selecting rows between previous days trigger timestamp and current days trigger. By using LEAD(), I was able to get below output. This only works if there are triggers every day.
Let's say, one trigger is missed. How do I rewrite the same query without much complexity but get the expected output.
Input Data: (if you observe, ctrl_dt 2023-02-16 is missed).
| CAPTURE_DT | CTRL_DT | INST |
|---|---|---|
| 2023-02-17 19:21:30.612814 | 2023-02-18 | AAA |
| 2023-02-16 19:18:16.045126 | 2023-02-17 | AAA |
| 2023-02-14 18:58:40.927273 | 2023-02-15 | AAA |
| 2023-02-13 21:43:38.832417 | 2023-02-14 | AAA |
| 2023-02-12 18:30:40.595363 | 2023-02-13 | AAA |
Expected Output:
| STARTTIME | ENDTIME | BS_DATE |
|---|---|---|
| 2023-02-16 19:18:16.045126 | 2023-02-17 19:21:30.612814 | 2023-02-17 |
| 2023-02-16 19:18:16.045126 | 2023-02-16 | |
| 2023-02-14 18:58:40.927273 | 2023-02-15 | |
| 2023-02-13 21:43:38.832417 | 2023-02-14 18:58:40.927273 | 2023-02-14 |
| 2023-02-12 18:30:40.595363 | 2023-02-13 21:43:38.832417 | 2023-02-13 |
| 2023-02-12 18:30:40.595363 | 2023-02-12 |
However, I'm getting
| STARTTIME | ENDTIME | BS_DATE |
|---|---|---|
| 2023-02-16 19:18:16.045126 | 2023-02-17 19:21:30.612814 | 2023-02-17 |
| 2023-02-14 18:58:40.927273 | 2023-02-16 19:18:16.045126 | 2023-02-16 |
| 2023-02-13 21:43:38.832417 | 2023-02-14 18:58:40.927273 | 2023-02-14 |
| 2023-02-12 18:30:40.595363 | 2023-02-13 21:43:38.832417 | 2023-02-13 |
| 2023-02-12 18:30:40.595363 | 2023-02-12 |
Query Used:
WITH
EVENT_TRIGGER
AS
(SELECT '2023-02-17 19:21:30.612814' CAPTURE_DT, '2023-02-18' CTRL_DT, 'AAA' INST FROM DUAL
UNION ALL
SELECT '2023-02-16 19:18:16.045126' CAPTURE_DT, '2023-02-17' CTRL_DT, 'AAA' INST FROM DUAL
UNION ALL
SELECT '2023-02-14 18:58:40.927273' CAPTURE_DT, '2023-02-15' CTRL_DT, 'AAA' INST FROM DUAL
UNION ALL
SELECT '2023-02-13 21:43:38.832417' CAPTURE_DT, '2023-02-14' CTRL_DT, 'AAA' INST FROM DUAL
UNION ALL
SELECT '2023-02-12 18:30:40.595363' CAPTURE_DT, '2023-02-13' CTRL_DT, 'AAA' INST FROM DUAL)
SELECT LEAD (CAPTURE_DT) OVER (PARTITION BY INST ORDER BY CTRL_DT DESC) AS STARTTIME,
CAPTURE_DT AS ENDTIME,
TO_DATE (CTRL_DT, 'YYYY-MM-DD') - 1 AS BS_DATE
FROM EVENT_TRIGGER
WHERE INST = 'AAA';
You can generate a calendar and then use a PARTITIONed OUTER JOIN:
WITH EVENT_TRIGGER (CAPTURE_DT, CTRL_DT, INST ) AS (
SELECT TIMESTAMP '2023-02-17 19:21:30.612814', DATE '2023-02-18', 'AAA' FROM DUAL UNION ALL
SELECT TIMESTAMP '2023-02-16 19:18:16.045126', DATE '2023-02-17', 'AAA' FROM DUAL UNION ALL
SELECT TIMESTAMP '2023-02-14 18:58:40.927273', DATE '2023-02-15', 'AAA' FROM DUAL UNION ALL
SELECT TIMESTAMP '2023-02-13 21:43:38.832417', DATE '2023-02-14', 'AAA' FROM DUAL UNION ALL
SELECT TIMESTAMP '2023-02-12 18:30:40.595363', DATE '2023-02-13', 'AAA' FROM DUAL
),
calendar (day) AS (
SELECT min_dt + LEVEL - 1 AS day
FROM (
SELECT MIN(TRUNC(capture_dt)) AS min_dt,
MAX(TRUNC(capture_dt)) AS max_dt
FROM event_trigger
)
CONNECT BY min_dt + LEVEL - 1 <= max_dt
)
SELECT LAG(e.capture_dt) OVER (PARTITION BY e.inst ORDER BY c.day)
AS STARTTIME,
e.CAPTURE_DT AS ENDTIME,
c.day AS BS_DATE
FROM calendar c
LEFT OUTER JOIN EVENT_TRIGGER e
PARTITION BY (e.inst)
ON (
c.day <= e.capture_dt
AND e.capture_dt < c.day + 1
)
WHERE e.inst = 'AAA'
ORDER BY c.day DESC;
Which, outputs:
| STARTTIME | ENDTIME | BS_DATE |
|---|---|---|
| 2023-02-16 19:18:16.045126000 | 2023-02-17 19:21:30.612814000 | 2023-02-17 00:00:00 |
| null | 2023-02-16 19:18:16.045126000 | 2023-02-16 00:00:00 |
| 2023-02-14 18:58:40.927273000 | null | 2023-02-15 00:00:00 |
| 2023-02-13 21:43:38.832417000 | 2023-02-14 18:58:40.927273000 | 2023-02-14 00:00:00 |
| 2023-02-12 18:30:40.595363000 | 2023-02-13 21:43:38.832417000 | 2023-02-13 00:00:00 |
| null | 2023-02-12 18:30:40.595363000 | 2023-02-12 00:00:00 |