SQL查询修正需求：按日统计当月白班/夜班总重量结果错误

原SQL可正确统计当日白班（06:05-18:05）、夜班（18:05-次日06:05）的总重量（TotalWeight），但修改后尝试统计当月每日班次重量时结果不符合预期。需要修正SQL，使其正确输出每日的白班总重量（DayshiftTotal）、夜班总重量（NightshiftTotal）及当日总重量。

原有正确查询（当日统计） #

-- 正确的当日班次重量统计
SELECT
    CASE
        WHEN CAST(CreateTime AS TIME) BETWEEN '06:05:00' AND '18:05:00' THEN '白班'
        ELSE '夜班'
    END AS Shift,
    SUM(Weight) AS TotalWeight
FROM YourTable
WHERE CAST(CreateTime AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY
    CASE
        WHEN CAST(CreateTime AS TIME) BETWEEN '06:05:00' AND '18:05:00' THEN '白班'
        ELSE '夜班'
    END;

错误的修改查询（当月每日统计） #

-- 错误的当月每日班次重量统计
SELECT
    CAST(CreateTime AS DATE) AS RecordDate,
    CASE
        WHEN CAST(CreateTime AS TIME) BETWEEN '06:05:00' AND '18:05:00' THEN SUM(Weight)
        ELSE 0
    END AS DayshiftTotal,
    CASE
        WHEN CAST(CreateTime AS TIME) NOT BETWEEN '06:05:00' AND '18:05:00' THEN SUM(Weight)
        ELSE 0
    END AS NightshiftTotal,
    SUM(Weight) AS TotalWeight
FROM YourTable
WHERE DATEPART(MONTH, CreateTime) = DATEPART(MONTH, GETDATE())
    AND DATEPART(YEAR, CreateTime) = DATEPART(YEAR, GETDATE())
GROUP BY CAST(CreateTime AS DATE), CAST(CreateTime AS TIME);

样本数据 #

预期结果 #

修正后的SQL查询 #

SELECT
    -- 修正日期归属：次日06:05前的记录归到前一日的夜班
    CASE
        WHEN CAST(CreateTime AS TIME) < '06:05:00' THEN DATEADD(DAY, -1, CAST(CreateTime AS DATE))
        ELSE CAST(CreateTime AS DATE)
    END AS RecordDate,
    -- 统计当日白班总重量
    SUM(CASE WHEN CAST(CreateTime AS TIME) BETWEEN '06:05:00' AND '18:05:00' THEN Weight ELSE 0 END) AS DayshiftTotal,
    -- 统计当日夜班总重量
    SUM(CASE WHEN CAST(CreateTime AS TIME) NOT BETWEEN '06:05:00' AND '18:05:00' THEN Weight ELSE 0 END) AS NightshiftTotal,
    SUM(Weight) AS TotalWeight
FROM YourTable
WHERE 
    -- 筛选归属当月的所有班次记录
    CASE
        WHEN CAST(CreateTime AS TIME) < '06:05:00' THEN DATEADD(DAY, -1, CAST(CreateTime AS DATE))
        ELSE CAST(CreateTime AS DATE)
    END BETWEEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) 
        AND EOMONTH(GETDATE())
GROUP BY
    CASE
        WHEN CAST(CreateTime AS TIME) < '06:05:00' THEN DATEADD(DAY, -1, CAST(CreateTime AS DATE))
        ELSE CAST(CreateTime AS DATE)
    END
ORDER BY RecordDate;

修正说明 #

• 日期归属修正：原错误查询直接用CreateTime的日期作为记录日期，但夜班中次日06:05之前的记录应归属前一日（比如2024-05-02 05:50:00属于2024-05-01的夜班），通过CASE语句调整日期归属，确保班次归属逻辑正确。
• 聚合逻辑修正：原错误查询在GROUP BY中包含了CAST(CreateTime AS TIME)，导致按时间点拆分记录，无法得到每日合计。修正后将SUM嵌套在CASE中，按调整后的RecordDate分组，实现每日班次重量的汇总。
• 当月数据筛选修正：原筛选条件仅按CreateTime的年月筛选，会漏掉上月末属于当月第一天夜班的记录（比如2024-04-30 20:00:00属于2024-05-01的夜班）。修正后用调整后的RecordDate筛选当月数据，确保所有归属当月的班次记录都被统计。

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