We help IT Professionals succeed at work.

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

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

Co-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 (
FROM @table
UNION ALL
CASE WHEN DATEADD(DAY,7,Enddate) <= (SELECT Enddate FROM @table WHERE personID = cte.personID)
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
``````

Results:
cte2.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
Data Analyst

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).