# Counting difference of two dates in a PL SQL FUNCTION, should be returing number of days

I have the following output:

from_date until_date
17.03.2020 18.05.2020
18.05.2020 08.06.2020
21.12.2020 01.03.2021
01.03.2021 11.03.2021
19.10.2021 22.10.2021
10.01.2022 14.01.2022
14.01.2022 NULL

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

### >Solution :

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