troubleshooting Question

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

Avatar of Jolynn Haney
Jolynn HaneyFlag for United States of America asked on
Microsoft SQL Server
3 Comments1 Solution67 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros