SELECT query over an interval of 1 day and use analytic functions LEAD()

Question

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';

Answer 1

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

fiddle