sql - SQLServer - 在两个日期之间，生成年份、ISO 周以及开始和结束日期的表

Question

我正在尝试为两个日期之间的所有周生成一个年份、ISO 周数和周开始/结束日期表。

这有效，除非第 53 周溢出到新的一年。在这种情况下，2020 年第 53 周应从 2020 年 12 月 28 日到 2021 年 1 月 3 日。相反，因为我按 ISO 周和年分组，所以我得到了 2020 年第 53 周 2020-12-28 到 2020-12-31 的一条记录，然后是 2021 年第 53 周 2021-01-01 到 2021 年的另一条错误记录- 01-03。

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           MIN(report_date)                AS week_beginning_date,
           MAX(report_date)                AS week_ending_date
      FROM report_dates
  GROUP BY DATEPART(YEAR,     report_date), 
           DATEPART(ISO_WEEK, report_date)
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

我也尝试手动将 week_ending_date 计算为 week_beginning_date + 6 - 但我仍然得到从 1 月开始的 2021 年第 53 周的错误记录。我可以再添加一个过滤器来删除额外的记录——也许使用滞后函数来检测连续的第 53 周记录并删除第二个记录——但这似乎比必要的解决方案更复杂。有没有更简单的方法来做到这一点？

这是在 SQLServer

标签： sqlsql-servertsql

Answer 1

当然，支持 ISO_WEEK 是相当愚蠢的，但是没有 ISO_YEAR，只有 YEAR/WEEK 的组合才有用。

这会修改您现有的查询来计算 ISO 周的星期四，基于 ISO 日历的定义，每个星期四总是在正确的年份：

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     D.thursday) AS report_year,
           DATEPART(ISO_WEEK, D.thursday) AS report_week,
           MIN(report_date)               AS week_beginning_date,
           MAX(report_date)               AS week_ending_date
    FROM report_dates 
                      -- get thursday of week
      CROSS APPLY(VALUES(CAST(DATEADD(DAY, (DATEDIFF(DAY, 0, report_date) / 7) * 7, 3) AS DATE))) AS D(thursday)
  GROUP BY D.thursday
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

见小提琴

顺便说一句，这是日历表的完美用例。而不是一遍又一遍地重复相同的计算（日期的星期永远不会改变），你只做一次。您可以将您需要的任何日期计算添加为新列，用户无需考虑正确的算法。

编辑：

如果您想获得整周，您可以根据星期四开始/结束一周，然后您不需要一周中的其他日子：

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211031';

;WITH report_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd) + 1)  
               report_date = DATEADD(DAY, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           DATEADD(DAY, -3,   report_date) AS week_beginning_date,
           DATEADD(DAY,  3,   report_date) AS week_ending_date
    FROM report_dates 
    WHERE report_date = DATEADD(DAY, (DATEDIFF(DAY, 0, report_date) / 7) * 7, 3) 
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

小提琴

这可以进一步简化为在最初的选择中每周只有一个星期四:-)

DECLARE @windowStart DATE = '20200101',
        @windowEnd   DATE = '20211030';

;WITH report_dates AS (
        SELECT 
           TOP ((DATEDIFF(DAY, @windowStart, @windowEnd))/7 +1) 
               -- start on a thursday 
               report_date = DATEADD(WEEK, 
                                     ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                     DATEADD(DAY, (DATEDIFF(DAY, 0, @windowStart) / 7) * 7, 3)) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
report_weeks AS (
    SELECT DATEPART(YEAR,     report_date) AS report_year,
           DATEPART(ISO_WEEK, report_date) AS report_week,
           DATEADD(DAY, -3,   report_date) AS week_beginning_date,
           DATEADD(DAY,  3,   report_date) AS week_ending_date
    FROM report_dates 
)
SELECT * FROM report_weeks ORDER BY week_beginning_date

小提琴