按7天分组日期，排除特定日期

Question

按7天分组日期，排除特定日期

sqlsql-servert-sqldatesql-server-2012

3

我需要查询，按照每七天一个组的方式从每个月的开始进行分组日期。问题在于我必须排除某些日期，特别是节假日前/后和节假日当天。在我的DateDay维度中，有一列指示它是哪种类型的日期。以下是11月份的日历示例：

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

我需要这样分组：

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

如果这样的组少于7天，则查询不应返回该组。

如果需要更多细节，请告诉我。

编辑：我不确定是否有帮助，但我编写了一个可以计算一周内正确天数的查询。

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

并且结果：

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

编辑2：我希望输出的是那些组中包含的日期ID。我指的是我的DateDay维度中的主键DTD_GID列作为ID。因此，对于第1组，我将获得以下列表：

- Dodzik

1

请指定您期望的详细输出。您的期望输出不清楚。 - Mansoor

@Mansoor 我在最后一次编辑中添加了预期输出 - Dodzik

如果我提供一个按组返回日期对的查询，会怎样？ - Salman A

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Salman A · Accepted Answer

以下是一种解决方案，可以给出每个7天范围的开始和结束日期：

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

输出：

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

这是它的工作原理：

第一个CTE移除假期并为剩余行分配组编号。连续的日期获得相同的组号 (请参见此问题)。
第二个CTE为每个组中的每一行指定另一个组编号。第1-7行获得0，8-14行获得1，以此类推。
最后，您根据组号对结果进行分组。