sql - Recurring date table -
i got table lot of scheduled events. want create datawarehouse table coming events setup table. how next week? events starts every 5 min, hourly, 3-hourly, daily , 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
if rdbms supports recursive ctes, this:
with futurecte ( select event_name, dateadd(mi,intervallroundup5min,next_time) nexttime ,intervallroundup5min table1 union select event_name, dateadd(mi,intervallroundup5min,nexttime) ,intervallroundup5min futurecte dateadd(mi,intervallroundup5min,nexttime) <= '2015-06-11' --end date ) select event_name, nexttime futurecte
modify end date whatever value want. sql fiddle
Comments
Post a Comment