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

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!
Jolynn HaneyData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
*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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jolynn HaneyData AnalystAuthor 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).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.