Sophisticated sql date logic: how can I plan my reports?

Question

I have a table that looks something like this (simplified):

*ScheduledReports* ReportID StartDate Frequency Interval (1=months, 2=days) 

So, if I wanted to run the report every 3 days, the frequency would be 3, and the interval would be 2.

I am trying to write a stored procedure that we can run once a day, which will run all the reports from the table that is scheduled for today (or should have been started since the last time the stored procedure was run).

The stored procedure also has access to lastRunTime (the last time this stored procedure was run).

This is what my query looks like:

 -- get monthly reports that should be run SELECT reportID FROM ScheduledReports WHERE intervalType = 1 AND dateadd(m, ( frequency * ceiling(( --sql server calculates datediff of months based on the actual int of the month --not if it a true month later, so the following is necessary to check for --a dayOfMonth difference CASE WHEN startDate > @lastRunTime THEN 0 WHEN day(startDate) > day(@lastRunTime) THEN datediff(m, startDate, @lastRunTime) - 1 ELSE datediff(m, startDate, @lastRunTime) END ) / (frequency*1.0)) ), startDate) BETWEEN @lastRunTime AND getDate() UNION ALL -- get weekly reports that should be run SELECT reportID FROM ScheduledReports WHERE intervalType = 2 AND dateadd(d, ( frequency * ceiling(( CASE WHEN startDate > @lastRunTime THEN 0 ELSE datediff(d, startDate, @lastRunTime) END ) / (frequency*1.0) )), startDate) BETWEEN @lastRunTime AND getDate() 

However, there is something with logic. What is wrong with my logic? How can i do this?

Answer 1

We managed to fix the error - we had to add 1 in the case, and not subtract, because we want to get the next execution date, not the previous one. Case of the month changed:

  CASE WHEN startDate > @lastRunTime THEN 0 WHEN day(startDate) > day(@lastRunTime) THEN datediff(m, startDate, @lastRunTime) ELSE datediff(m, startDate, @lastRunTime) + 1 END 

and daytime event:

 CASE WHEN startDate > @lastRunTime THEN 0 ELSE datediff(d, startDate, @lastRunTime) + 1 END 

And now it works great :)

Answer 2

The stored procedure also has access to lastRunTime (the last time this stored procedure was run).

You do not need to know the last time each report was prepared? And not the last time this sproc was launched? Each time you run sproc, each report may or may not be prepared. To find out if the interval for each report for each report has passed (3 days, 1 month, etc.), you need to know when the last report was published.

What if you add the LastReportRun date column to your table. Then do not test today's date in @lastRunTime, but rather against LastReportRun.

 Report Run Today; Today = 2012/04/15 ID StartDate Freqcy Interval LastReportRun -- ---------- ------ -------- ---------------- 1 2000/01/01 1 Days 2012/04/14 1 day ago; print it 2 2000/01/01 14 Days 2012/04/09 6 days ago; don`t print it 3 2000/01/01 3 Months 2012/01/13 > 3 mos ago; print it 4 2000/01/01 3 Months 2012/01/17 < 3 mos ago; don`t print it