SQL Server - Breakdown date period-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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