How to iterate over a date range in PL/SQL

Question

I need to write a report that generates summary totals against a table with date ranges for each record.

table data:
option   start_date   end_date
opt1     6/12/2009    6/19/2009
opt1     6/3/2009     6/13/2009
opt2     6/5/2009     6/6/2009

What I want out is basically this:

date       option    count
6/1/2009   opt1      0
6/1/2009   opt2      0
6/2/2009   opt1      0
6/2/2009   opt2      0
6/3/2009   opt1      0
6/3/2009   opt2      1

I am having a hard time figuring out how to iterate over a date range. I am sure this is some simple cursor that could be created for this but I am at a loss. Preferably in PL/SQL

UPDATE:

I ended up using the example here to accomplish what I wanted to do. This creates a function that generates a table of dates.

Answer 1

One solution that I use for this is to convert the date range into an integer range that you can use in a for loop, then convert back to a date to do stuff with it. You can't do any joins or anything this way, but it's a much smaller solution that those already posted:

declare
  start_date number;
  end_date number;
  business_date varchar2(8);
begin
  start_date := to_number(to_char(to_date('2013-04-25', 'yyyy-MM-dd'), 'j'));
  end_date := to_number(to_char(to_date('2013-05-31', 'yyyy-MM-dd'), 'j'));
  for cur_r in start_date..end_date loop
    business_date := to_char(to_date(cur_r, 'j'), 'yyyy-MM-dd');
    dbms_output.put_line(business_date);
  end loop;
end;

Answer 2

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

Answer 3

Just as an addition to the other techniques, one way I iterate over dates is the following:

/* List of days for the past year, starting with today at midnight */
SELECT TRUNC(SYSDATE) + 1 - LEVEL AS today,
       TRUNC(SYSDATE) + 2 - LEVEL AS tomorrow
FROM DUAL
CONNECT BY LEVEL <= 365

Answer 4

Using while loop (better)

declare
 dfrom date;
 dtill date;
 day date;
begin
    dfrom := TO_DATE('09.09.1988', 'dd.mm.yyyy');
    dtill := TO_DATE('19.09.1988', 'dd.mm.yyyy');
    day := dfrom;

WHILE day <= dtill
LOOP
   DBMS_OUTPUT.PUT_LINE(day);
   day := day + 1;
END LOOP;
    
end;
/

//using cursor
declare
 dfrom date;
 dtill date;
begin
    dfrom := TO_DATE('09.09.1988', 'dd.mm.yyyy');
    dtill := TO_DATE('19.09.1988', 'dd.mm.yyyy');
FOR cur IN (
      SELECT dfrom + LEVEL - 1 AS today
        FROM dual
        CONNECT BY LEVEL <= dtill - dfrom + 1
    ) LOOP
    DBMS_OUTPUT.PUT_LINE(cur.today);
    END LOOP;
    
end;
/

Answer 5

Here is an answer based on an answer above: It uses a start and end date:

It lists all of the days of 07/01/2013 to 07/31/2013. Easily adaptable to any date range.

SELECT to_date('07/01/2013', 'mm/dd/yyyy') + LEVEL - 1 AS today
FROM dual
CONNECT BY LEVEL <= to_date('07/31/2013', 'mm/dd/yyyy') - to_date('07/01/2013', 'mm/dd/yyyy') + 1;

Answer 6

declare 
v_curr_date  date;
for i in to_number(to_char(p_date_from ,'j')) .. to_number(to_char(p_date_to 
,'j')) loop

 v_curr_date = to_date(to_char(i),'j'); 
 --make any operation on v_curr_date (like insert into table)

end loop;    

end;

Answer 7

This type of query is best handled if you have a second "utility" table, which you can use for just about any query where you need to convert ranges into specific buckets. The utility table is nothing more than a list of numbers:

CREATE TABLE Iterator (Counter NUMBER);

COUNTER
-------
      0
      1
      2
      3 
...
    100 (or however many rows you want to include)

IF we assume that you want to display 30 days, e.g.

SELECT   TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter thedate
       , i.My_option
       , count(y.My_option)
    FROM ( SELECT DISTINCT
                  i2.Counter
                , y.My_option
             FROM iterator i2
                , YourTable y
            WHERE i2.Counter < 5
         ) i
           LEFT OUTER JOIN yourtable y 
                           ON  TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter 
                               >= y.start_date
                           AND TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter 
                               <  y.end_date
                           AND y.My_option = i.My_option
GROUP BY TO_DATE('6/1/2009', 'MM/DD/YYYY') + i.counter
       , i.My_option
ORDER BY 1
       , 2;

The idea is that you create a Cartesian product between your iterator table and your table with the range, then filter out all the cases where your range conditions aren't met. You can use this in many places, and is one of the best examples why it is better to model your data with ranges as opposed to discrete intervals - because you can always convert easily to discrete intervals using this technique.

edit: I really shouldn't use BETWEEN for date range queries - I changed it to >= <