Repeating rows of date intervals based on another table in Oracle

dateoracle

I have a table of maintenance plans with date intervals between each required maintenance period.

Fiddle: http://sqlfiddle.com/#!4/af742 

create table maint_sched (maint_id number, start_date date, day_interval number);
insert into maint_sched values (1, date '2015-01-01', 90);
insert into maint_sched values (2, date '2015-04-01', 200);
insert into maint_sched values (3, date '2015-07-01', 30);

I need to write a query which generates rows for all maintenance schedules up to a finite point in the future (e.g. 12 months). 

MAINT_ID   DATE_DUE
--------  ---------
1          1-Jan-15
1          1-Apr-15
2          1-Apr-15
1         30-Jun-15
3          1-Jul-15
3         31-Jul-15
3         30-Aug-15
1         28-Sep-15
3         29-Sep-15
2         18-Oct-15
3         29-Oct-15
3         28-Nov-15
1         27-Dec-15
3         28-Dec-15

I've been able to get as far as generating fixed intervals with CONNECT BY tricks, but it starts doing something recursive when I try joining it to a table other than dual. 

-- This generates a list of every five days for 36 months
SELECT (sysdate + (5 * level)) AS duedate
FROM dual
CONNECT BY (sysdate + (5 * level)) < add_months(sysdate, 36)

I have a feeling this is the right approach, but I could use some help making it work!

Best Answer

Returning maintenance dates for the next 365 days:

variable n number;
exec :n := 365;

with generator as
(
  select rownum - 1 as r from dual connect by level <= (select max(ceil(:n / day_interval)) from maint_sched)
)
select ms.maint_id, ms.start_date + g.r * ms.day_interval as date_due
from maint_sched ms join generator g on (ms.start_date + g.r * ms.day_interval between sysdate and sysdate + :n)
order by 2, 1;

  MAINT_ID DATE_DUE
---------- ---------
         1 30-JUN-15
         3 01-JUL-15
         3 31-JUL-15
         3 30-AUG-15
         1 28-SEP-15
         3 29-SEP-15
         2 18-OCT-15
         3 29-OCT-15
         3 28-NOV-15
         1 27-DEC-15
         3 28-DEC-15
         3 27-JAN-16
         3 26-FEB-16
         1 26-MAR-16
         3 27-MAR-16
         3 26-APR-16
         2 05-MAY-16
Related Question