[SOLVED] SQL Server - Breakdown date period

SQL Server - Breakdown date period

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

Solution

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;