We help IT Professionals succeed at work.

Break large date range in to smaller 7-day date ranges

Jolynn Haney
Jolynn Haney asked
on
Hello
I am trying to break a larger date range into a set of smaller 7-day date ranges so that I can identify events that occur at least once within each 7-day range.

For example:

personID     Startdate           Enddate
1                    2017-01-01      2017-02-10
2                   2018-06-14      2018-06-29

This is my desired output.

personID     Startdate           Enddate
1                    2017-01-10      2017-01-16
1                    2017-01-17      2017-01-23
1                    2017-01-24      2017-01-30
1                    2017-01-31      2017-02-06
1                    2017-02-07      2017-02-10
2                    2018-06-14      2018-06-20
2                    2018-06-21      2018-06-27
2                    2018-06-28      2018-06-29

Note that that last date range may be shorter than 7 days as in the above example.
I am able to break the range in to calendar weeks, but I am stuck on how to break the range into 7 days periods that are independent of calendar weeks.

I appreciate your help - thanks!
Comment
Watch Question

View Solution Only

Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
*Edited, copied the wrong query*

You can use a CTE for this:
DECLARE @table TABLE (personID INT, Startdate DATETIME, Enddate DATETIME)

INSERT INTO @table (personID,Startdate,Enddate)
VALUES (1,'2017-01-10','2017-02-10')
INSERT INTO @table (personID,Startdate,Enddate)
VALUES (2,'2018-06-14','2018-06-29')

;with cte AS (
	SELECT personId,Startdate,DATEADD(DAY,6,Startdate) AS Enddate
	FROM @table
	UNION ALL
	SELECT personId,DATEADD(DAY,1,Enddate) AS Startdate,
		CASE WHEN DATEADD(DAY,7,Enddate) <= (SELECT Enddate FROM @table WHERE personID = cte.personID) 
		THEN DATEADD(DAY,7,Enddate)
		ELSE (SELECT Enddate FROM @table WHERE personID = cte.personID) END AS Enddate
	FROM cte
	WHERE Enddate < (SELECT Enddate FROM @table WHERE personID = cte.personID)
) 
SELECT * 
FROM cte
ORDER BY personID,Startdate,Enddate

Open in new window


Results:
cte2.pngcte2.png
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Perfect use of the tally table in SQL.  I'll use an inline tally table, up to 100 rows (weeks in this case), because it's very fast, and it's easy to use.

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT tbl.personID, DATEADD(DAY, 7 * t.number, Startdate) AS Startdate,
    CASE WHEN DATEADD(DAY, 7 * t.number + 6, Startdate) > Enddate
         THEN Enddate ELSE DATEADD(DAY, 7 * t.number + 6, Startdate) END AS Enddate
FROM @table tbl
INNER JOIN cteTally100 t ON t.number BETWEEN 0 AND (DATEDIFF(DAY, Startdate, Enddate) - 1) / 7
ORDER BY personID, Startdate
Jolynn HaneyData Analyst

Author

Commented:
Thanks so much - both solutions worked, but I just needed to tweak Dustin's solution so that the new Startdate is one day greater then the previous Enddate (not the same as).