By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36241 in the last week).
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
✓
hidden batch(es)
CREATE TABLE table_name (from_date, until_date) AS
SELECT DATE '2020-03-17', DATE '2020-05-18' FROM DUAL UNION ALL
SELECT DATE '2020-05-18', DATE '2020-06-08' FROM DUAL UNION ALL
SELECT DATE '2020-12-21', DATE '2021-03-01' FROM DUAL UNION ALL
SELECT DATE '2021-03-01', DATE '2021-03-11' FROM DUAL UNION ALL
SELECT DATE '2021-10-19', DATE '2021-10-22' FROM DUAL UNION ALL
SELECT DATE '2022-01-10', DATE '2022-01-14' FROM DUAL UNION ALL
SELECT DATE '2022-01-14', NULL FROM DUAL;
7 rows affected
hidden batch(es)
SELECT from_date,
until_date,
COALESCE(until_date, TRUNC(SYSDATE)) - from_date
+ CASE
WHEN LAG(until_date) OVER (ORDER BY FROM_DATE) = from_date
THEN 0
ELSE 1
END
AS difference
FROM table_name;
FROM_DATE
UNTIL_DATE
DIFFERENCE
2020-03-17 00:00:00
2020-05-18 00:00:00
63
2020-05-18 00:00:00
2020-06-08 00:00:00
21
2020-12-21 00:00:00
2021-03-01 00:00:00
71
2021-03-01 00:00:00
2021-03-11 00:00:00
10
2021-10-19 00:00:00
2021-10-22 00:00:00
4
2022-01-10 00:00:00
2022-01-14 00:00:00
5
2022-01-14 00:00:00
154
…
hidden batch(es)
BEGIN
FOR r IN (
SELECT from_date,
until_date,
COALESCE(until_date, TRUNC(SYSDATE)) - from_date
+ CASE
WHEN LAG(until_date) OVER (ORDER BY FROM_DATE) = from_date
THEN 0
ELSE 1
END
AS difference
FROM table_name
) LOOP
DBMS_OUTPUT.PUT_LINE( r.from_date || ', ' || r.until_date || ', ' || r.difference );
END LOOP;
END;
/
