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
I can do:
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.
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.
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 :)
EDIT: My sample fiddle: http://sqlfiddle.com/#!3/25d0d/1
My example:
task starts at 2013-10-22 8:00 and ends at 2013-10-22 21:00
procedure starts as 2013-10-22 10:00
first record has send time 14:30 so nothing to send
procedure starts as 2013-10-22 12:00
first record has send time 14:30 so nothing to send
procedure starts as 2013-10-22 14:00
first record has send time 14:30 so nothing to send
procedure starts as 2013-10-22 16:00
first record has send time 14:30 so send mail about that task
next message should be send about 17:45
procedure starts as 2013-10-22 18:00
first record has send time 17:45 so send mail about that task
next message should be send about 19:22
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