sql - 复杂的sql日期逻辑: How can I schedule my reports?

Question

我有一个看起来像这样的表(简化):

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

因此，如果我想每 3 天运行一次报告，则频率为 3，间隔为 2。

我正在尝试编写一个我们可以每天运行一次的存储过程，它将运行今天安排的表中的所有报告(或者自上次运行存储过程以来应该运行)。

存储过程还可以访问 lastRunTime(此存储过程的最后一次运行时间)。

这是我的查询到目前为止的样子:

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

虽然逻辑上有些不对劲。我的逻辑有什么问题？我怎样才能做到这一点？

Answer 1

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

您不需要知道每份报告的上次生成时间吗？不是最后一次运行这个 sproc 吗？每次运行 sproc 时，可能会或可能不会生成每个报告。为了知道每个报告的间隔是否已经过去(3 天、1 个月等)，您需要知道该报告上次生成的时间。

如果将 LastReportRun 日期列添加到表中会怎样。然后不要针对@lastRunTime 测试今天的日期，而是针对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