TSQL - рекурсивно выбрать дату между двумя заданными датами

У меня есть таблица в моей БД с именем Tasks. Каждая запись в этой таблице имеет 2 поля: StartDate, EndDate
Мне нужно создать рекурсивную хранимую процедуру, которая будет отправлять почту в середине этих дат.
Например:
Начало 2013-10-22 12:00:00:000
Конец 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')

а затем проверьте, больше ли сейчас этой даты, если да, то я могу отправить почту.

Но мне нужно сделать это рекурсивно: первый основной должен быть отправлен в середине, второй в 1/4, третий в 1/8 и т. д. и последний, когда осталось 2 часа.

Моя первая идея заключалась в том, чтобы добавить столбец в мою таблицу и сохранить в нем дату последнего основного, но я хотел бы избежать изменения таблиц.

Я думаю, что рекурсивный выбор был бы лучше, но любые идеи о том, как это решить, приветствуются :)

EDIT: Мой образец скрипта: http://sqlfiddle.com/#!3/25d0d/1

Мой пример:
задача начинается 22.10.2013 в 8:00 и заканчивается в 21:00 22.10.2013.

процедура начинается с 2013-10-22 10:00
первая запись имеет время отправки 14:30, поэтому отправлять нечего

процедура начинается с 2013-10-22 12:00
первая запись имеет время отправки 14:30, поэтому отправлять нечего

процедура начинается с 2013-10-22 14:00
первая запись имеет время отправки 14:30, поэтому отправлять нечего

процедура начинается с 2013-10-22 16:00
первая запись имеет время отправки 14:30, поэтому отправьте письмо об этой задаче

следующее сообщение должно быть отправлено около 17:45

процедура начинается с 2013-10-22 18:00

первая запись имеет время отправки 17:45, поэтому отправьте письмо об этой задаче

следующее сообщение должно быть отправлено около 19:22

процедура начинается как 2013-10-22 20:00
первая запись имеет время отправки 19:22, поэтому почта должна быть отправлена,
но поскольку с 19:22 до 21:00 меньше 2 часов нет почты нужно


sql tsql recursive-query sql-server-2005
person Misiu    schedule 22.10.2013    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Невозможно протестировать SQL Server 2005, но в SQL Server 2008 вы можете использовать рекурсивное общее табличное выражение (заменив фиксированные даты ниже параметрами вашей процедуры). Первая часть получает первое время, вторая часть продолжает вычислять время между последним временем и конечным временем, пока разница между временами не станет меньше 4 часов;

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;

SQLfiddle для тестирования.

РЕДАКТИРОВАТЬ: Чтобы получить задачи, которые имеют время отправки почты за последние 2 часа (т.е. должны генерировать почту), вы можете использовать что-то вроде;

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() до нескольких часов.

person Joachim Isaksson    schedule 22.10.2013
comment
Потрясающе :) На 2005 работает нормально. И еще. Поскольку я буду запускать процедуру в работе каждые 2 часа, как я могу выбрать те задачи, для которых я хочу отправить почту? Я изменю свою скрипку, чтобы показать свою идею - person Misiu; 22.10.2013
comment
Я не заметил редактирования в Вашем ответе. сразу проверю :) - person Misiu; 22.10.2013