I have the following output:

I need to count the days between these two dates, second date inclusively, with this logic:

(18.05.2020 -  17.03.2020) 1 = 63

The next row begins at the same day as it ends in the first row, then the 18.05.2020 must not be counted in the days difference, so:

08.06.2020 - 18.05.2020

if the until_date is null then it will be:

sysdate-from_date

but what im struggling to do is to get next element and previous element values in a loop so I can compare them

CodePudding user response：

You can use the LAG analytic function to find the previous until_date:

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;

Which, for the sample data:

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;

Outputs:

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	null	154

If you want it in PL/SQL then wrap the query in a cursor and loop through the cursor in PL/SQL.

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

Or you can do exactly the same thing by storing the previous until_date in a PL/SQL variable:

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

db<>fiddle here