如何修改SQL实现输入日期范围偏移-1个月汇总托盘数据

核心调整思路 #

原有代码通过拆分日期的年、月部分做等值判断，既无法直接适配偏移1个月的跨年逻辑，也无法命中日期字段上的索引，还容易漏掉带时分秒的日期末尾数据。
调整时直接计算偏移后的真实统计日期边界，用闭开区间做范围匹配，自动处理跨年、不同月份天数、闰年等边界场景，不需要写多分支判断：

• 统计起始点：用户输入起始月往前推1个月的当月1日，若起始月为1月自动取上一年12月1日
• 统计截止点：用户输入结束月的当月1日，匹配所有早于该时间点的记录，等价于覆盖到结束月往前推1个月的最后一天23:59:59

偏移规则验证 #

• 输入@Year=2022、@StartMonth=1、@EndMonth=5时，自动计算边界为2021-12-01 00:00:00至2022-05-01 00:00:00，实际统计区间为2021年12月1日到2022年4月30日，符合要求
• 输入@Year=2021、@StartMonth=1、@EndMonth=12时，自动计算边界为2020-12-01 00:00:00至2021-12-01 00:00:00，实际统计区间为2020年12月1日到2021年11月30日，符合要求

优化后代码 #

@Year int, 
@StartMonth int, 
@EndMonth int

-- 提前计算偏移后的日期边界，避免WHERE中重复计算
DECLARE @ActualStartDate DATE = DATEFROMPARTS(
                                CASE WHEN @StartMonth = 1 THEN @Year - 1 ELSE @Year END,
                                CASE WHEN @StartMonth = 1 THEN 12 ELSE @StartMonth - 1 END,
                                1
                              )
DECLARE @ActualEndCutoff DATE = DATEFROMPARTS(@Year, @EndMonth, 1)

SELECT        
    SUM(Pallets) AS Expr1, 
    DATEPART(mm, Date) AS Expr2, 
    DATEPART(yyyy, Date) AS Expr3
FROM dbo.PalettesData
WHERE Date >= @ActualStartDate 
  AND Date < @ActualEndCutoff
GROUP BY DATEPART(yyyy, Date), DATEPART(mm, Date)
ORDER BY Expr3, Expr2

兼容说明 #

如果使用的是SQL Server 2008及更早版本（不支持DATEFROMPARTS函数），可以替换日期计算逻辑为DATEADD写法，边界计算结果完全一致：

-- 旧版本SQL Server替换边界计算部分即可
DECLARE @ActualStartDate DATE = DATEADD(month, -1, CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@StartMonth AS VARCHAR(2)) + '-01' AS DATE))
DECLARE @ActualEndCutoff DATE = CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@EndMonth AS VARCHAR(2)) + '-01' AS DATE)

内容的提问来源于stack exchange，提问作者Luke Y.