SQL按日期分组动态统计未来两周数据的问题排查与实现

你原有写法的核心错误有三个：

• DATEPART(dw, 日期)的返回值固定在1-7区间，对应一周7天，永远不会返回8-17的值，这是第二周所有列统计结果全为0的直接原因。
• 该函数的返回值受会话级DATEFIRST参数影响，不同环境、不同配置下同一个星期几对应的返回值可能不同，直接导致跨日执行时统计偏移。
• 两周内同名星期几（比如两个周三）的dw返回值完全一致，根本无法通过这个值区分属于第一周还是第二周，必然出现数据混淆。

核心逻辑是直接计算业务日期和执行当日的天数偏移量，用0-13的固定值对应从当日开始未来14天的每一列，完全不依赖星期维度规则，不受环境参数影响，也不会出现跨周同星期数据串列的问题。注意先把日期的时分秒部分去掉，避免时间精度导致的边界数据漏算。

静态列版本（固定14列，执行效率高） #

-- 定义统计起始日，截断时分秒，默认从执行当日开始统计
DECLARE @StartDate DATE = CAST(GETDATE() AS DATE);

SELECT
    ITT1.Code,
    -- 按日期偏移匹配对应列，别名可根据自身需求替换为星期标识
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 0 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day1],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 1 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day2],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 2 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day3],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 3 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day4],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 4 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day5],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 5 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day6],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 6 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day7],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 7 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day8],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 8 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day9],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 9 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day10],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 10 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day11],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 11 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day12],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 12 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day13],
    CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = 13 THEN OWOR.PlannedQty END), 0) AS INT) AS [Day14]
FROM OWOR
LEFT JOIN ITT1 ON OWOR.ITEMCODE = ITT1.Father
WHERE 
    DueDate >= @StartDate 
    AND DueDate < DATEADD(DAY, 14, @StartDate)
    AND OWOR.UserSign = '108'
GROUP BY ITT1.Code

动态列版本（列名自动显示对应日期+星期） #

如果需要列名自动匹配当日对应的星期和实际日期（比如10-16_Wed格式），可以用动态SQL拼接，无需手动修改别名：

DECLARE @StartDate DATE = CAST(GETDATE() AS DATE);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @i INT = 0;

-- 循环拼接14天的统计列
WHILE @i < 14
BEGIN
    DECLARE @ColDate DATE = DATEADD(DAY, @i, @StartDate);
    DECLARE @ColName VARCHAR(20) = CONCAT(FORMAT(@ColDate,'MM-dd'),'_',DATENAME(WEEKDAY,@ColDate));
    SET @SQL = CONCAT(
        ISNULL(@SQL+',',''),
        'CAST(ISNULL(SUM(CASE WHEN DATEDIFF(DAY, @StartDate, DueDate) = ',@i,' THEN OWOR.PlannedQty END),0) AS INT) AS [',@ColName,']'
    );
    SET @i = @i + 1;
END

-- 拼接完整查询语句并执行
SET @SQL = CONCAT(
    'SELECT ITT1.Code,',@SQL,
    ' FROM OWOR LEFT JOIN ITT1 ON OWOR.ITEMCODE = ITT1.Father',
    ' WHERE DueDate >= @StartDate AND DueDate < DATEADD(DAY,14,@StartDate) AND OWOR.UserSign = ''108''',
    ' GROUP BY ITT1.Code'
);

EXEC sp_executesql @SQL, N'@StartDate DATE', @StartDate = @StartDate;

内容的提问来源于stack exchange，提问作者Kay