SQL Server - Breakdown date period

Question

I want to create a query that breakdowns a date period into 10 days sub-periods

So a period of 2022-04-15 to 2022-05-01 should be broken into

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

The period could be one day (2022-04-15 to 2022-04-15) or even years

Any help appreciated

Thank you

score 4 · Accepted Answer · answered Jun 29 '22 at 08:28

A Tally would be a much more performant approach:

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;

Salman A · Answer 2 · 2022-06-29T08:22:37.603

2

You can use a recursive cte. A rough outline is as follows:

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<>Fiddle

edited Jun 29 '22 at 08:22

answered Jun 29 '22 at 08:14

Salman A

262,204
82
430
521

For a large range of dates this'll be much slower than a set based method like a Tally. SQL is terribly slow at iteration. – Thom A Jun 29 '22 at 08:15
Yes but first we have to establish that the approach is "unacceptably" slow. – Salman A Jun 29 '22 at 08:21
Speed is OK. The only issue is that the last period is always 10 days in your example. Last period should stop on the end of the period – PanosPlat Jun 29 '22 at 08:29
Just top it off using a case expression (case when dateadd(day, -1, date2_) > enddate then enddate else ...) – Salman A Jun 29 '22 at 08:39

novice in DotNet · Answer 3 · 2022-06-29T08:48:25.600

Does this help?

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<>Fiddle

SQL Server - Breakdown date period

3 Answers3