Return Date Ranges based based on a given interval between two given dates in SQL-CodePudding

I am trying to get a list of all DateRanges of a given interval between two dates.

For example if I have the dates 2015-04-01 and 2015-06-20 and when the interval is 20 the result should be

DateFrom       DateTo
------------------------
2015-04-01  2015-04-21
2015-04-22  2015-05-12
2015-05-13  2015-06-02
2015-06-03  2015-06-20
-------------------

Here is the query I am trying

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
DECLARE @Interval int = 20
SET @StartDateTime = '2015-04-01'
SET @EndDateTime = '2015-06-20';

WITH DateRange(DateStart,DateEnd) AS 
(
    SELECT @StartDateTime, DATEADD(d,@Interval,@StartDateTime)
    UNION ALL
    SELECT DATEADD(d,1,DateEnd), DATEADD(d,@Interval,DateEnd)
    FROM DateRange 
    WHERE DateEnd <= @EndDateTime
)
SELECT CAST(DateStart as date) DateStart
    , CAST(DateEnd as Date) DateEnd
FROM DateRange
OPTION (MAXRECURSION 0)
GO

Its returning the below result which is not exactly as my expected result above

DateStart   DateEnd
2015-04-01  2015-04-21
2015-04-22  2015-05-11
2015-05-12  2015-05-31
2015-06-01  2015-06-20
2015-06-21  2015-07-10

Here as you can see, there is an extra row outside the given dates and also interval between second row onward is 19 days, not 20. I understand its because I am adding a Day to the first field after union all

Please help me to fix this query or please suggest a better way to implement the same.

CodePudding user response：

Changes to your recursive cte should be

use CASE to check for ending date. If it is greater than the required end date, set it to @EndDateTime

Also the WHERE condition should be

WHERE DateEnd < @EndDateTime


WITH DateRange(DateStart,DateEnd) AS 
( 

SELECT @StartDateTime,DATEADD(day, @Interval, @StartDateTime)

UNION ALL

SELECT dateadd(day, 1, DateEnd), 
       case when DATEADD(day, @Interval   1, DateEnd) <= @EndDateTime
            then DATEADD(day, @Interval   1, DateEnd)
            else @EndDateTime
            end
FROM  DateRange 
WHERE DateEnd < @EndDateTime

)

CodePudding user response：

If you want intervals starting every @Intervalth day

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
declare @Interval int=20
SET @StartDateTime = '2015-04-01'
SET @EndDateTime = '2015-06-20';

WITH DateRange(DateStart,DateEnd) AS 
(
    SELECT @StartDateTime, DATEADD(d, @Interval - 1, @StartDateTime)
    UNION ALL
    SELECT dateadd(d, @Interval, DateStart),DATEADD(d, @Interval, DateEnd)
    FROM DateRange 
    WHERE DateEnd < @EndDateTime - 1
)
SELECT cast(DateStart as date) DateStart,cast(DateEnd as Date) DateEnd
FROM DateRange
OPTION (MAXRECURSION 0)