SQL编写自动适配日期范围的SUM聚合查询无需手动修改参数

需求说明 #

原有SUM聚合查询硬编码了-6的月份偏移参数，每月执行需手动修改，需替换为自动计算的日期逻辑，固定统计区间规则：

• 区间起点：上一年度的1月1日
• 区间终点：上一年度与当前查询日期同月同日的日期

计算示例：查询执行当日为2022年6月22日时，自动统计2021-01-01至2021-06-22范围内的数据，无需手动调整参数。

改造后SQL语句 #

SELECT 
    SUM(N_EXCL), 
    S_CUSTNO, 
    S_CUSTNAME1, 
    D_INVOICEDATE 
FROM sao.INVOICE_P i 
WHERE  
    D_INVOICEDATE >= DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1)
    AND D_INVOICEDATE < DATEADD(YEAR, -1, GETDATE())
GROUP BY S_CUSTNO, S_CUSTNAME1, D_INVOICEDATE

逻辑说明 #

• 区间起点计算：DATEFROMPARTS(YEAR(GETDATE()) - 1, 1, 1) 取当前日期的年份减1，直接拼接为该年度1月1日的日期值，不受查询当日的月、日影响，始终定位到上一年元旦。
• 区间终点计算：DATEADD(YEAR, -1, GETDATE()) 将当前日期直接往前偏移1年，自动匹配和当日月、日完全一致的上一年日期，完全替代原来硬编码的月份偏移逻辑。
• 精度适配：如果D_INVOICEDATE字段带有时分秒精度，需要包含截止日全天数据的话，可以将终点条件调整为D_INVOICEDATE < DATEADD(DAY, 1, CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE))，避免漏掉截止日0点之后的业务数据。
• 低版本兼容：如果使用SQL Server 2008及更早不支持DATEFROMPARTS的版本，区间起点可以替换为兼容写法DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)，计算结果完全一致。

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