TSQL - 在 2 个给定日期之间递归选择日期 [英] TSQL - Recursively select date between 2 given dates
问题描述
我的数据库中有名为 Tasks 的表.该表中的每条记录都有 2 个字段:StartDate、EndDate
我需要创建递归存储过程，将在这些日期的中间发送邮件.
例如:
开始时间为 2013-10-22 12:00:00:000
结束是 2013-10-24 12:00:00:000
I have table in my db called Tasks.
Every record in that table has 2 fields: StartDate, EndDate
I need to create recursive stored procedure that will send mails in middle of those dates.
For example:
Start is 2013-10-22 12:00:00:000
End is 2013-10-24 12:00:00:000
我能做到:
SELECT DATEADD(ms,
DATEDIFF(ms,'2013-10-22 12:00:00:000', '2013-10-24 12:00:00:000')/2,
'2013-10-22 12:00:00:000')
然后检查现在是否大于那个日期，如果是，那么我可以发送邮件.
and then check if now is greater than that date, if Yes then I can send mail.
但我需要递归地做到这一点:第一个主要必须在中间发送，第二个在 1/4，第三个在 1/8 等，最后在剩下 2 小时时发送.
But I need to do that recursively: first main must be send in middle, second in 1/4, third in 1/8 etc and at the last when there are 2 hours left.
我的第一个想法是在我的表中添加列并在其中存储最后一个 main 的日期，但我想避免修改表.
My first idea was to add column to my table and store date of last main in it, but I would like to avoid modifying tables.
我认为递归选择会更好，但欢迎任何关于如何解决这个问题的想法:)
I think it recursive select would be better, but any ideas on how to solve that are welcome :)
我的示例小提琴:http://sqlfiddle.com/#!3/25d0d/1
我的例子:
任务开始于 2013-10-22 8:00 结束于 2013-10-22 21:00
My example:
task starts at 2013-10-22 8:00 and ends at 2013-10-22 21:00
程序开始于2013-10-22 10:00
第一条记录的发送时间为 14:30 所以没有什么可发送的
procedure starts as 2013-10-22 10:00
first record has send time 14:30 so nothing to send
程序开始于2013-10-22 12:00
第一条记录的发送时间为 14:30 所以没有什么可发送的
procedure starts as 2013-10-22 12:00
first record has send time 14:30 so nothing to send
程序开始于2013-10-22 14:00
第一条记录的发送时间为 14:30 所以没有什么可发送的
procedure starts as 2013-10-22 14:00
first record has send time 14:30 so nothing to send
程序开始于2013-10-22 16:00
第一条记录的发送时间为 14:30，因此请发送有关该任务的邮件
procedure starts as 2013-10-22 16:00
first record has send time 14:30 so send mail about that task
下一条消息应该在 17:45 左右发送
next message should be send about 17:45
程序开始于2013-10-22 18:00
第一条记录的发送时间为 17:45，因此请发送有关该任务的邮件
procedure starts as 2013-10-22 18:00
first record has send time 17:45 so send mail about that task
下一条消息应该在 19:22 左右发送
next message should be send about 19:22
程序开始于2013-10-22 20:00
第一条记录的发送时间为 19:22 所以应该发送邮件，
但是因为从 19:22 到 21:00 不到 2 个小时，不需要邮件
procedure starts as 2013-10-22 20:00
first record has send time 19:22 so mail should be sended,
but because from 19:22 till 21:00 is less that 2 hours no mail is needed
推荐答案
无法在 SQL Server 2005 上测试，但在 SQL Server 2008 上您可以使用递归公用表表达式(用您的过程参数替换下面的固定日期).第一部分获取第一次，第二部分不断计算上次和结束时间之间的时间，直到时间差小于4小时；
Can't test on SQL Server 2005, but on SQL Server 2008 you can use a recursive common table expression (replacing the fixed dates below with your procedure parameters). The first part gets the first time, the second part keeps calculating the time between the last time and the end time until the difference between the times is less than 4 hours;
WITH cte AS (
SELECT DATEADD(ms,
DATEDIFF(ms, '2013-10-22 12:00:00:000',
'2013-10-24 12:00:00:000')/2,
'2013-10-22 12:00:00:000'
) a
UNION ALL
SELECT DATEADD(ms, DATEDIFF(ms,cte.a, '2013-10-24 12:00:00:000')/2, cte.a)
FROM cte
WHERE DATEDIFF(hour, cte.a, '2013-10-24 12:00:00:000') >= 4
)
SELECT * FROM cte;
要获取邮件时间在过去 2 小时内的任务(即应该生成邮件)，您可以使用类似的东西；
To get the tasks that have a mail time that was in the last 2 hours (ie that should generate a mail), you can use something like;
WITH cte AS (
SELECT taskid,enddate, DATEADD(s,
DATEDIFF(s, startdate, enddate)/2, startdate) tm
FROM Tasks
UNION ALL
SELECT taskid,enddate, DATEADD(ms, DATEDIFF(ms,cte.tm, enddate)/2, cte.tm)
FROM cte
WHERE DATEDIFF(hour, cte.tm, enddate) >= 4
)
SELECT taskid, tm FROM cte WHERE tm < GETDATE() AND DATEDIFF(hour, tm, GETDATE()) < 2
如果作业以不规则的间隔运行，您可能希望将 GETDATE() 截断为几个小时.
If the job runs at somewhat irregular intervals, you may want to truncate GETDATE() to just hours.
这篇关于TSQL - 在 2 个给定日期之间递归选择日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
