Dodzik Dodzik - 10 days ago 7
SQL Question

Group dates by 7 days excluding specific dates

I need query, where I could group dates by every 7 days from beginning of the month. The problem is I have to exclude some days, specifically days before/after holidays and holidays. In my DateDay dimension there's a column, thats indicates which type of day it is. Example of calendar for November:

DTD_GID DTD_Date DTD_DayType
20161101 2016-11-01 2 --holiday was on 2016-10-31
20161102 2016-11-02 0
20161103 2016-11-03 0
20161104 2016-11-04 0
20161105 2016-11-05 0
20161106 2016-11-06 0
20161107 2016-11-07 0
20161108 2016-11-08 0
20161109 2016-11-09 0
20161110 2016-11-10 2
20161111 2016-11-11 1--public holiday
20161112 2016-11-12 2
20161113 2016-11-13 0
20161114 2016-11-14 0
20161115 2016-11-15 0
20161116 2016-11-16 0
20161117 2016-11-17 0
20161118 2016-11-18 0
20161119 2016-11-19 0
20161120 2016-11-20 0
20161121 2016-11-21 0
20161122 2016-11-22 0
20161123 2016-11-23 0
20161124 2016-11-24 0
20161125 2016-11-25 0
20161126 2016-11-26 0
20161127 2016-11-27 0
20161128 2016-11-28 0
20161129 2016-11-29 0
20161130 2016-11-30 0


I need to group it like that:

1: 2016-11-02 - 2016-11-08 (inclusive)
2: 2016-11-13 - 2016-11-19
3: 2016-11-20 - 2016-11-26


If such group would have less than 7 days, it shouldn't be returned by query.

Let me know if you need more details.

EDIT: I'm not sure if it will help, but I wrote query that's counting proper days in weeks

SELECT
DTD_DTMGID
,CONVERT(VARCHAR(5), DATEADD(WK, Week, 0), 103) + ' - ' + CONVERT(VARCHAR(5), DATEADD(DD, 6, DATEADD(WK, Week, 0)), 103) AS Week
,Cnt
FROM (
SELECT
DTD_DTMGID
, DATEDIFF(WK, 0, DTD_DATE) AS Week
, COUNT(*) AS Cnt
FROM DIM_DateDay
WHERE DTD_DayType = 0
GROUP BY DTD_DTMGID ,DATEDIFF(WK, 0, DTD_DATE)
) AS X
ORDER BY DTD_DTMGID


and result:

DTD_DTMGID Week Cnt
201301 31/12 - 06/01 2
201301 07/01 - 13/01 5
201301 14/01 - 20/01 7
201301 21/01 - 27/01 7
201301 28/01 - 03/02 5
201302 28/01 - 03/02 2


EDIT2: As output I expect ID's of days that are in those groups. As ID's I mean DTD_GID column which is primary key in my DateDay dimension.
So for group 1) I'd get following list:

20161102
20161103
20161104
20161105
20161106
20161107
20161108

Answer

Here is one solution that gives you start and end date of each 7-day range:

WITH CTE1 AS (
    SELECT DTD_Date, DATEDIFF(DAY, ROW_NUMBER() OVER (ORDER BY DTD_Date), DTD_Date) AS Group1 
    FROM #Table1
    WHERE DTD_DayType = 0
), CTE2 AS (
    SELECT DTD_Date, Group1, (ROW_NUMBER() OVER (PARTITION BY Group1 ORDER BY Group1) - 1) / 7 AS Group2
    FROM CTE1
)
SELECT MIN(DTD_Date) AS DTD_From, MAX(DTD_Date) AS DTD_Upto, COUNT(DTD_Date) AS C
FROM CTE2
GROUP BY Group1, Group2
ORDER BY DTD_From
-- HAVING COUNT(*) >= 7

Output:

DTD_From   | DTD_Upto   | C
-----------+------------+--
2016-11-02 | 2016-11-08 | 7
2016-11-09 | 2016-11-09 | 1
2016-11-13 | 2016-11-19 | 7
2016-11-20 | 2016-11-26 | 7
2016-11-27 | 2016-11-30 | 4

Here is how it works:

  • The first CTE removes holidays and assigns a group number to remaining rows. Consecutive dates get same group number (see this question).
  • The second CTE assigns another group number to each row in each group. Row number 1-7 get 0, 8-14 get 1, and so on.
  • Finally you group the results by the group numbers.
Comments