Group dates by 7 days excluding specific dates

Question

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:

Specify your detail expected output.Your expected output is not clear. — Mansoor, Nov 24 '16 at 09:44
What if I provide a query that returns pairs of dates by group? — Salman A, Nov 24 '16 at 10:16

score 3 · Accepted Answer · edited May 23 '17 at 10:30

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.

That's exactly what I was looking for. Thanks! – Dodzik Nov 24 '16 at 11:01 — Dodzik, Nov 24 '16 at 11:01

Group dates by 7 days excluding specific dates

1 Answers1