CODEWITHSUNDEEP
sqlsql-servert-sqllogic
froadie
froadie

Reputation: 83053

Complicated sql date logic: How can I schedule my reports?

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

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

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

I'm trying to write a stored procedure that we can run once a day, that will run all reports from the table that are scheduled for today (or should have been run since the last time we ran the stored procedure).

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

This is what my query looks like so far:

-- 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's 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()

There is something off with the logic though. What is wrong with my logic? How can I accomplish this?

Upvotes: 4

Views: 741

Answers (2)

froadie
froadie

Reputation: 83053

We managed to fix the bug - we were supposed to be adding 1 in the case, not subtracting, because we want to get the next run date, not the previous. Changed the month case to:

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

and the day case to:

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

And now it is working perfectly :)

Upvotes: 0

Scott
Scott

Reputation: 95

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

Don't you need to know the last time each report was produced? And not the last time this sproc was run? Each time you run the sproc, each report may or may not be produced. In order to know if the interval for each report has passed for each report (3 days, 1 month, etc) you need to know when THAT report was last produced.

What if you add a LastReportRun date column to your table. Then don't test todays date against @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

Upvotes: 1

Related Questions