SQL Server语言 - 明细日期周期

Question

我想创建一个查询，将一个日期周期分解为10天的子周期

所以周期为2022-04-15至2022-05-01应该分解成

2022-04-15 2022-04-24
2022-04-25 2022-05-01 

周期可以是一天(2022-04-15到2022-04-15)甚至是年

感谢您的帮助

谢谢

Answer 1

一个计数将是一个更高效的方法:

DECLARE @Start date = '20220415',
@End date = '20220501',
@Days int = 10;
WITH N AS (
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
UNION ALL
SELECT TOP (DATEDIFF(DAY,@Start,@End)/@Days)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM N N1, N N2, N N3, N N4) --Up to 1,000 rows. Add more cross joins for more rows
SELECT DATEADD(DAY, T.I*@Days,@Start),
CASE WHEN DATEADD(DAY, ((T.I+1)*@Days)-1,@Start) > @End THEN @END ELSE DATEADD(DAY, ((T.I+1)*@Days)-1,@Start) END
FROM Tally T;

Answer 2

您可以使用递归cte。大致大纲如下:

create table #test (
id int identity primary key,
date1 date,
date2 date
);
insert into #test (date1, date2) values
('2022-04-15', '2022-05-01'),
('2022-04-15', '2022-04-15');
with rcte as (
select id, date1 as date1_, dateadd(day, 10, date1) as date2_, date2 as enddate
from #test
union all
select id, date2_, dateadd(day, 10, date2_), enddate
from rcte
where date2_ <= enddate
)
select id, date1_, dateadd(day, -1, date2_)
from rcte
order by 1, 2

,DB&lt的在小提琴

Answer 3

这有帮助吗?

declare @fromdate date=cast('2022-04-15' as date);
declare @todate date=cast('2022-05-01' as date);
WITH cte_dates(tendays) 
AS (
SELECT 
@fromdate
UNION ALL
SELECT    
case when dateadd(d,10,tendays) > @todate then @todate else dateadd(d,10,tendays) end
FROM    
cte_dates
WHERE tendays < dateadd(d,-9,@todate)
)
SELECT 
tendays,case when dateadd(d,9,tendays) > @todate then @todate else dateadd(d,9,tendays) end
FROM 
cte_dates;

,DB&lt的在小提琴