How to Iterate Over a Date Range in Pl/Sql

How to iterate over a date range in PL/SQL

You will need some sort of calendar to loop through a range of date. I have built one using the connect by level trick. You can then join the calendar with your data (cross join since you want a row even when there is no option for that day):

SQL> WITH calendar AS (
  2     SELECT to_date(:begin_date, 'mm/dd/yyyy') + ROWNUM - 1 c_date
  3       FROM dual
  4      CONNECT BY LEVEL <= to_date(:end_date, 'mm/dd/yyyy') 
                             - to_date(:begin_date, 'mm/dd/yyyy') + 1
  5  )
  6  SELECT c_date "date", d_option "option", COUNT(one_day)
  7    FROM (SELECT c.c_date, d.d_option,
  8                  CASE
  9                     WHEN c.c_date BETWEEN d.start_date AND d.end_date THEN
 10                      1
 11                  END one_day
 12             FROM DATA d, calendar c)
 13   GROUP BY c_date, d_option
 14  ORDER BY 1,2;

date        option COUNT(ONE_DAY)
----------- ------ --------------
01/06/2009  opt1                0
01/06/2009  opt2                0
02/06/2009  opt1                0
02/06/2009  opt2                0
03/06/2009  opt1                1
03/06/2009  opt2                0
04/06/2009  opt1                1
04/06/2009  opt2                0
05/06/2009  opt1                1
05/06/2009  opt2                1
06/06/2009  opt1                1
06/06/2009  opt2                1

12 rows selected

PL SQL iterate loop through date range

To me, it looks as if everything is, actually, OK with code you wrote, because active_date gets its new value:

SQL> set serveroutput on;
SQL> declare
  2    start_date   date := to_date('06.01.2021', 'dd.MM.yyyy');
  3    end_date     date := to_date('26.05.2021', 'dd.mm.yyyy');
  4    active_date  date;
  5    start_number number;
  6    end_number   number;
  7  begin
  8    start_number := to_number(to_char(start_date, 'j'));
  9    end_number   := to_number(to_char(end_date, 'j'));
 10    active_date  := start_date;
 11
 12    for cur_r in start_number..end_number
 13    loop
 14      dbms_output.put_line('Active_date = ' || to_char(active_date, 'dd.mm.yyyy'));
 15      /* Commented, as I don't have your tables nor data
 16         INSERT INTO test_tbl
 17           SELECT snap_date
 18           FROM s_act
 19           WHERE snap_date = active_date;
 20      */
 21      active_date := trunc(active_date) + 7;
 22    end loop;
 23    -- move COMMIT out of the loop!
 24    commit;
 25  end;
 26  /
Active_date = 06.01.2021
Active_date = 13.01.2021
Active_date = 20.01.2021
<snip>
Active_date = 06.09.2023
Active_date = 13.09.2023

PL/SQL procedure successfully completed.

SQL>

---

You said

When I execute this script, only one date 06.01.2021 is written to the table through all iterations.

This is piece of code responsible for that:

INSERT INTO test_tbl
  SELECT snap_date 
  FROM s_act
  WHERE snap_date = active_date;

I interpret it as:

• s_act table contains rows only with snap_date equal to 06.01.2021, or

• if it contains rows with other dates, maybe they contain a time component (hours, minutes, seconds) and where condition prevents them to be inserted. If that's so, try with

  where trunc(snap_date) = active_date
  

  and see what happens.

Oracle SQL Loop through Date Range

Here is how you might get a range of dates:

 SELECT DATE'2015-01-01' + LEVEL - 1
   FROM dual
CONNECT BY DATE'2015-01-01' + LEVEL - 1 < DATE'2015-02-01';

The above will get all dates in the range of January 1, 2015 to January 31, 2015.

What you could do, using the above, is plug in your start and end dates and create a CTE, then use an outer join on the dates:

WITH dr AS (
    SELECT DATE'2015-01-01' + LEVEL - 1 AS transaction_date
      FROM dual
   CONNECT BY DATE'2015-01-01' + LEVEL - 1 < DATE'2015-01-04'
)
SELECT V.VISIT_ID, dr.transaction_date
     , P.NAME_LAST, ETT.ENC_TRANS_TYPE_NAME
...
       ENCOUNTER_TRANSACTION ET RIGHT JOIN dr
    ON ET.ENCOUNTER_TRANSACTION_DATE = dr.transaction_date

UPDATE I had some time and I think I see how the above can be integrated into your query. I don't really have sample data for a SQL Fiddle (you have a lot of tables for that anyway). Here is where you might start, this should get all the appropriate visits plus every date along the range of dates on the visit (assuming no visit exceeds 30 days - adjust that accordingly):

WITH dr AS (
    SELECT LEVEL AS dd FROM dual
   CONNECT BY LEVEL <= 30 -- I'm assuming a max date range of 30; increase as you see fit
)
SELECT v.visit_id, v.start_date - 1 + dr.dd AS encounter_transaction_date
     , p.name_last, ett.enc_trans_type_name
  FROM visit v CROSS JOIN dr
 WHERE v.start_date - 1 + dr.dd < TRUNC(v.end_date) + 1
   AND v.institution_id = 1
   AND v.end_date IS NOT NULL
   AND v.voided_yn = 'N'
   AND v.care_setting_code = 'I'
   AND v.patient_team_id IN (16,17,18)
   AND v.start_date >= TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MONTH')
   AND v.end_date < TRUNC(CURRENT_DATE, 'MONTH');

Then I think your outer joins should be LEFT JOINs from there (at least, if I understand correctly which I may not:

WITH dr AS (
    SELECT LEVEL AS dd FROM dual
   CONNECT BY LEVEL <= 30 -- I'm assuming a max date range of 30; increase as you see fit
)
SELECT v.visit_id, v.start_date - 1 + dr.dd AS encounter_transaction_date
  FROM visit v CROSS JOIN dr
  LEFT JOIN encounter_transaction et
    ON v.visit_id = et.visit_id
   AND v.institution_id = et.institution_id
   AND TRUNC(v.start_date - 1 + dr.dd) = et.encounter_transaction_date
  LEFT JOIN encounter_transaction_type ETT
    ON et.encounter_type_id = ett.encounter_transaction_type_id
  LEFT JOIN local_provider lp
    ON et.ordering_provider_id = lp.local_provider_id
  LEFT JOIN person_identifier i
    ON i.identifier = lp.provider_number
   AND i.identifier_sys_id = lp.provider_number_sys_id
   AND i.identifier IN (
       '1234', --Smith
       '4321' --Jones ** you had an extra comma here!
)
  LEFT JOIN person p
    ON p.person_id = i.person_id
 WHERE v.start_date - 1 + dr.dd < TRUNC(v.end_date) + 1
   AND v.institution_id = 1
   AND v.end_date IS NOT NULL
   AND v.voided_yn = 'N'
   AND v.care_setting_code = 'I'
   AND v.patient_team_id IN (16,17,18)
   AND v.start_date >= TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MONTH')
   AND v.start_date < TRUNC(CURRENT_DATE, 'MONTH');

oracle loop over date

You can simply get the average value using the following query:

SELECT DATE,
       AVG (values)
  FROM table
 WHERE DATE BETWEEN DATE '2020-03-01' AND DATE '2020-03-03';

Or if you want to use the loop then use the query in FOR loop IN clause as follows:

SQL> DECLARE
  2  BEGIN
  3  FOR DATAS IN (
  4  SELECT DATE '2020-03-01' + LEVEL - 1 DT
  5    FROM DUAL CONNECT BY
  6  LEVEL <= DATE '2020-03-03' - DATE '2020-03-01' + 1
  7  ) LOOP
  8  DBMS_OUTPUT.PUT_LINE(DATAS.DT);
  9  -- YOUR_CODE_HERE
 10  END LOOP;
 11  END;
 12  /
01-MAR-20
02-MAR-20
03-MAR-20

PL/SQL procedure successfully completed.

SQL>

Loop through date range and insert when no data found

As a more concrete example of the MERGE approach already suggested:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
BEGIN
  MERGE INTO calendar ca
  USING (
    SELECT Insert_Data.Start_Date + level - 1 as cal_date
    FROM dual
    CONNECT BY level <= Insert_Data.End_Date - Insert_Data.Start_Date + 1
  ) t
  ON (t.cal_date = ca.cal_date)
  WHEN NOT MATCHED THEN INSERT VALUES (t.cal_date);
END Insert_Data;

It doesn't need to be a procedure at all, but that seems to be a requirement on its own. You can just run the merge as plain SQL, using your date range directly instead of through variables. (Or as bind variables, depending on how/where you're running this).

The USING clause is a generated table that creates all of the dates in the supplied range, using a common CONNECT BY method. The LEVEL pseudocolumn is similar to the loop you're trying to do; overall the inner query generates all dates in your range as an inline view, which you can then use to check against the actual table. The rest of the statement only inserts new records from that range if they don't already exit.

You could also do the same thing manually, and less efficiently, with a NOT EXISTS check:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
BEGIN
  INSERT INTO calendar
  WITH t AS (
    SELECT Insert_Data.Start_Date + level - 1 as cal_date
    FROM dual
    CONNECT BY level <= Insert_Data.End_Date - Insert_Data.Start_Date + 1
  )
  SELECT cal_date
  FROM t
  WHERE NOT EXISTS (
    SELECT 1
    FROM Calendar
    WHERE Calendar.cal_date = t.cal_date
  );
END Insert_Data;

SQL Fiddle.

---

You have a few other issues in your procedure.

This is redundant because of the form of cursor-for loop you're using:

  cal_v        calendar%rowtype;

You have an unnecessary nested block here; it doesn't hurt I suppose but it isn't adding anything either. The first BEGIN, DECLARE and the first END can be removed (and the alignment is a bit off):

  BEGIN  -- remove
  DECLARE -- remove
      CURSOR cal_c IS
      SELECT * 
      FROM Calendar;
  BEGIN
...
  END; -- remove
END Insert_Data;

The outer loop, and the entire cursor, isn't needed; it actually means you're repeating the inner loop which actually does the work (or tries to, the first time anyway) as many times as there are existing records in the calendar table, which is pointless and slow:

      FOR cal_v IN calc_c LOOP
       FOR date_v IN Insert_Data.Start_Date..Insert_Data.End_Date LOOP
...
       END LOOP;
      END LOOP;

The inner loop won't compile as you can't use dates for a range loop, only integers (giving PLS-00382):

       FOR date_v IN Insert_Data.Start_Date..Insert_Data.End_Date LOOP

The innermost select doesn't have an INTO; this won't compile either:

          SELECT * FROM calendar WHERE calc_v.calc_date = date_v;

The insert needs the value to be enclose in parentheses:

            INSERT INTO calendar VALUES date_v;

So if you really did want to do it this way you'd do something like:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
  tmp_date DATE;
BEGIN
  FOR i IN 0..(Insert_Data.End_Date - Insert_Data.Start_Date) LOOP
  BEGIN
  dbms_output.put_line(i);
    SELECT cal_date INTO tmp_date FROM calendar
    WHERE cal_date = Insert_Data.Start_Date + i;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      INSERT INTO calendar VALUES (Insert_Data.Start_Date + i);
  END;
  END LOOP;
END Insert_Data;

... but really, use merge.

Oracle SQL iterate through dates

You can identify all out-of-hours logons using:

SELECT logon_time,
       username
FROM   sessions
WHERE  EXTRACT( HOUR FROM CAST( logon_time AS TIMESTAMP ) ) < 6
OR     EXTRACT( HOUR FROM CAST( logon_time AS TIMESTAMP ) ) >= 20

If you want it for a particular date range then:

SELECT logon_time,
       username
FROM   sessions
WHERE  (   EXTRACT( HOUR FROM CAST( logon_time AS TIMESTAMP ) ) < 6
       OR  EXTRACT( HOUR FROM CAST( logon_time AS TIMESTAMP ) ) >= 20 )
AND    logon_time BETWEEN DATE '2016-05-01' AND SYSDATE

Will get the out-of-hours logons from midnight 1st May to the current time.

Generating dates between 2 given dates and looping in PL/SQL

Using the data that you have provided:

Query 1:

with dates as (select to_date('01-MAY-2013', 'DD-MON-YYYY') + level - 1 d_date
                 from dual
               connect by level <= add_months(to_date('01-MAY-2013', 'DD-MON-YYYY'), 1)
                                   - to_date('01-MAY-2013', 'DD-MON-YYYY'))
select d_date, count(bug_id) NO_OF_OPEN_BUGS
  from dates
  left outer join bugs on (d_date between REPORTED_DATE and nvl(CLOSED_DATE, d_date))
group by d_date
order by d_date

Output

|                     D_DATE | NO_OF_OPEN_BUGS |
|----------------------------|-----------------|
| May, 01 2013 00:00:00+0000 |               0 |
| May, 02 2013 00:00:00+0000 |               0 |
| May, 03 2013 00:00:00+0000 |               2 |
| May, 04 2013 00:00:00+0000 |               3 |
| May, 05 2013 00:00:00+0000 |               5 |
| May, 06 2013 00:00:00+0000 |               5 |
| May, 07 2013 00:00:00+0000 |               4 |
| May, 08 2013 00:00:00+0000 |               4 |
| May, 09 2013 00:00:00+0000 |               5 |
| May, 10 2013 00:00:00+0000 |               5 |
| May, 11 2013 00:00:00+0000 |               4 |
| May, 12 2013 00:00:00+0000 |               6 |
| May, 13 2013 00:00:00+0000 |               7 |
| May, 14 2013 00:00:00+0000 |               9 |
| May, 15 2013 00:00:00+0000 |               9 |
| May, 16 2013 00:00:00+0000 |               8 |
| May, 17 2013 00:00:00+0000 |               7 |
| May, 18 2013 00:00:00+0000 |               7 |
| May, 19 2013 00:00:00+0000 |               7 |
| May, 20 2013 00:00:00+0000 |               8 |
| May, 21 2013 00:00:00+0000 |               7 |
| May, 22 2013 00:00:00+0000 |               8 |
| May, 23 2013 00:00:00+0000 |               8 |
| May, 24 2013 00:00:00+0000 |               8 |
| May, 25 2013 00:00:00+0000 |               9 |
| May, 26 2013 00:00:00+0000 |               6 |
| May, 27 2013 00:00:00+0000 |               6 |
| May, 28 2013 00:00:00+0000 |               6 |
| May, 29 2013 00:00:00+0000 |               6 |
| May, 30 2013 00:00:00+0000 |               6 |
| May, 31 2013 00:00:00+0000 |               5 |

Query 2:

with dates as (select to_date('01-MAY-2013', 'DD-MON-YYYY') + level - 1 d_date
                 from dual
               connect by level <= add_months(to_date('01-MAY-2013', 'DD-MON-YYYY'), 1)
                                - to_date('01-MAY-2013', 'DD-MON-YYYY')),
tab as (select d_date, count(bug_id) NO_OF_OPEN_BUGS, dense_rank() over (order by count(bug_id) desc) MAX_FLAG
  from dates
  left outer join bugs on (d_date between REPORTED_DATE and nvl(CLOSED_DATE, d_date))
group by d_date)
select 'There were ' || NO_OF_OPEN_BUGS || 
       ' open bugs on '|| to_char(d_date, 'DD-MON-YYYY') ||
       '.' MSG from 
   tab where max_flag = 1;

Output:

|                                    MSG |
|----------------------------------------|
| There were 9 open bugs on 14-MAY-2013. |
| There were 9 open bugs on 25-MAY-2013. |
| There were 9 open bugs on 15-MAY-2013. |

Information from query 1 is used to generate query 2.

---

Related Topics