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

Can you clarify what you mean by "do it for the next week"? It's not clear what your problem is. — Patrick Tucci, Jun 10 '15 at 13:01
What RDBMS? Ideally, you could use a recursive CTE, I think. — Andrew, Jun 10 '15 at 13:05
I want a table with one row per job and eventtime, like a "calenderview" of all jobs for the coming next week.I´m using SQL 2008r2. — user3519275, Jun 10 '15 at 13:13

score 0 · Answer 1 · answered Jun 10 '15 at 13:16

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

Recurring date table

1 Answers1