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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
*Edited, copied the wrong query*
You can use a CTE for this:
Results:
cte2.png
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
Results:
cte2.png
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)
),
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
;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0)
),
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial