Avatar of Jolynn Haney
Jolynn Haney
Flag for United States of America

asked on 

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!
Microsoft SQL Server

Avatar of undefined
Last Comment
Jolynn Haney

8/22/2022 - Mon