#sql #sql-server #date #datetime #recursive-query
#sql #sql-сервер #Дата #дата-время #рекурсивный-запрос
Вопрос:
У меня есть две даты 21/10/2019 и 26/6/2031, и из этих двух мне нужен список дат с интервалом в три месяца, начиная с первой даты, подобной этой:
22/10/2019 | 21/01/2020
22/01/2020 | 21/04/2020
22/04/2020 | 21/07/2020
22/07/2020 | 21/10/2020
...
22/01/2031 | 21/04/2031
22/04/2031 | 26/06/2031
Я пытался использовать ROW_NUMBER() и DENSE_RANK() и LAG() для группировки полного списка дат между двумя датами, но, похоже, я не могу в этом разобраться. Я думаю, что мне может понадобиться как-то разделить это, но я не могу сделать это правильно.
Если вы не понимаете, пожалуйста, дайте мне знать. Я довольно новичок в этом 🙂
Комментарии:
1. Вы приняли ответ, который не соответствует опубликованному вами ожидаемому результату. Принятый ответ имеет Start_Date, который начинается 21 числа месяца и заканчивается 20 числа месяца, но вы очень явно опубликовали ожидаемый результат, где Start_Dates все 22 числа месяца и End_Date, который заканчивается 21 числа каждого месяца в возврате. Что правильно? Ваш ожидаемый результат или ответ, который вы приняли?
2. Я принял ответ, потому что код работал, и я просто изменил его, чтобы иметь нужные мне даты. Я не знал о рекурсивных запросах, так что это действительно помогло мне 🙂
3. Хорошо, спасибо. Инкрементные рекурсивные CTE тогда будут очень тяжелыми для системы, когда дело доходит до чтения памяти и процессора даже для сравнительно небольших задач. Вас интересует другой способ?
Ответ №1:
Вы можете использовать рекурсивный запрос:
with cte (dt, end_dt) as (
select @start_dt, @end_dt
union all
select dateadd(month, 3, dt), end_dt from cte where dt < end_dt
)
select dt,
case when dateadd(month, 3, dt) < end_dt
then dateadd(day, -1, dateadd(month, 3, dt))
else end_dt
end as end_dt
from cte
order by dt;
Если вам нужно сгенерировать более 100 кварталов, вам нужно добавить option (maxrecursion 0) в самом конце запроса.
Комментарии:
1. Возможно, вам захочется еще раз просмотреть результаты. В своем исходном сообщении вы указали, что начальные даты для каждой строки должны приходиться на 22-й день, а дата окончания в той же строке должна приходиться на 21-й день (за исключением последнего дня в серии), и этот код этого не делает.
2. Я принял ответ, потому что код работал, и я просто изменил его, чтобы иметь нужные мне даты. Я не знал о рекурсивных запросах, так что это действительно помогло мне 🙂
3. Хорошо, спасибо. Инкрементные рекурсивные CTE тогда будут очень тяжелыми для системы, когда дело доходит до чтения памяти и процессора даже для сравнительно небольших задач. Вас интересует другой способ?
Ответ №2:
Это также может быть сделано с использованием подхода, основанного на подсчете или числах. Верхний предел tally_cte равен 12 ^ 5 = 248,832 (больше, чем может дать рекурсия, и его можно увеличить настолько, насколько это необходимо).
declare
@start_dt datetime='2019-10-21',
@end_dt datetime='2031-06-26'
;with
n(n) as (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n)),
tally_cte(n) as (
select row_number() over (order by (select null))
from n n1 cross join n n2 cross join n n3 cross join n n4 cross join n n5)
select t.*, cast(dt.dt as date) start_dt, cast(dateadd(MONTH, 3, dt.dt) as date) end_dt
from tally_cte t
cross apply (select dateadd(month, (n-1)*3, @start_dt) dt) dt
where n<datediff(month, @start_dt, @end_dt)/3;
N start_dt end_dt
1 2019-10-21 2020-01-21
2 2020-01-21 2020-04-21
3 2020-04-21 2020-07-21
...
45 2030-10-21 2031-01-21
Комментарии:
1. Правильная идея, но вы не делали start_dt 22-го числа каждого месяца, как сказал OP, которого он хотел на основании своего образца вывода, и при этом вы не указывали окончательную дату (вам не хватает последних двух строк серии).