clear markdown feedback
clear markdown feedback
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; /
1 rows affected dbms_output: 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)


DECLARE v_until_date DATE; v_diff NUMBER; BEGIN FOR r IN ( SELECT from_date, until_date FROM table_name ORDER BY from_date ) LOOP v_diff := COALESCE(r.until_date, TRUNC(SYSDATE)) - r.from_date + CASE WHEN v_until_date = r.from_date THEN 0 ELSE 1 END; DBMS_OUTPUT.PUT_LINE( r.from_date || ', ' || r.until_date || ', ' || v_diff ); v_until_date := r.until_date; END LOOP; END; /
1 rows affected dbms_output: 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)