Recurring date table

Question

I got a table with a lot of scheduled events. I want to create a datawarehouse table with all coming events from this setup table. How could I do it for the next week? Some events starts every 5 min, hourly, 3-hourly, daily and so.

Example table

Event_name, last_run, next_time, Intervallroundup5min

Job1, 2015-06-10 14:48:03.147, 2015-06-10 14:49:00.000 , 5

Job2, 2015-06-10 12:27:09.637, 2015-06-10 15:25:00.000, 180

Answer 1

If your RDBMS supports recursive CTEs, you could do this:

with futurecte as 
(
  select event_name,
  dateadd(mi,Intervallroundup5min,next_time) as NExtTime
  ,Intervallroundup5min
  from
  table1
  union all
  select
  event_name,
  dateadd(mi,Intervallroundup5min,nexttime)
  ,Intervallroundup5min
  from
  futurecte
  where
  dateadd(mi,Intervallroundup5min,nexttime) <= '2015-06-11'  --end date
  )
  select
  event_name,
  nexttime
  from
  futurecte

Modify the end date to whatever value you want. SQL Fiddle