按账户统计各月最近及次最近年份销售额的SQL实现需求

针对12个月份，基于当前日期，按account_no（账户编号）级别分别统计每个月份的最近年份销售额（命名为MostRecent[月份]）和次最近年份销售额（命名为2ndMostRecent[月份]）。

规则示例 #

• 若当前日期为2022年10月6日：
  • NovemberMostRecent统计2021年11月的销售额，November2ndMostRecent统计2020年11月的销售额；
  • JuneMostRecent统计2022年6月的销售额，June2ndMostRecent统计2021年6月的销售额；
• 当日期进入2022年11月后，NovemberMostRecent将统计2022年11月数据，November2ndMostRecent统计2021年11月数据。

用户尝试的SQL代码 #

SELECT NovemberMostRecent_Value = 
       sum(case when datepart(year,tran_date) = datepart(year, getdate())
AND DATEPART(month, tran_date) = 11 then value else 0 end)
       NovemberSecondMostRecent_Value = 
       sum(case when datepart(year,tran_date) = datepart(year, getdate())-1
AND DATEPART(month, tran_date) = 11 then value else 0 end)

源数据片段 #

期望结果表 #

正确SQL实现方案 #

核心逻辑是：对每个目标月份，先判断当前日期的月份是否大于等于该月份，以此确定最近统计年份是当前年还是当前年-1，次最近年份则对应最近年份-1。

以下是完整的SQL代码（以11月和6月为例，其他月份可按相同逻辑扩展）：

SELECT 
    account_no,
    -- 11月最近年份销售额
    NovemberMostRecent = SUM(CASE 
        WHEN DATEPART(MONTH, GETDATE()) >= 11 
            THEN CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) AND DATEPART(MONTH, tran_date) = 11 THEN value ELSE 0 END
            ELSE CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 1 AND DATEPART(MONTH, tran_date) = 11 THEN value ELSE 0 END
    END),
    -- 11月次最近年份销售额
    November2ndMostRecent = SUM(CASE 
        WHEN DATEPART(MONTH, GETDATE()) >= 11 
            THEN CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 1 AND DATEPART(MONTH, tran_date) = 11 THEN value ELSE 0 END
            ELSE CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 2 AND DATEPART(MONTH, tran_date) = 11 THEN value ELSE 0 END
    END),
    -- 6月最近年份销售额
    JuneMostRecent = SUM(CASE 
        WHEN DATEPART(MONTH, GETDATE()) >= 6 
            THEN CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) AND DATEPART(MONTH, tran_date) = 6 THEN value ELSE 0 END
            ELSE CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 1 AND DATEPART(MONTH, tran_date) = 6 THEN value ELSE 0 END
    END),
    -- 6月次最近年份销售额
    June2ndMostRecent = SUM(CASE 
        WHEN DATEPART(MONTH, GETDATE()) >= 6 
            THEN CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 1 AND DATEPART(MONTH, tran_date) = 6 THEN value ELSE 0 END
            ELSE CASE WHEN DATEPART(YEAR, tran_date) = DATEPART(YEAR, GETDATE()) - 2 AND DATEPART(MONTH, tran_date) = 6 THEN value ELSE 0 END
    END)
FROM 你的表名 -- 替换为实际表名
GROUP BY account_no;

代码说明 #

• 外层CASE判断当前月份是否大于等于目标月份：
  • 如果是，说明当前年的该月份已过，最近年份取当前年，次最近取当前年-1；
  • 如果否，说明当前年的该月份未到，最近年份取当前年-1，次最近取当前年-2；
• 内层CASE匹配对应年份和月份的销售额，求和得到结果；
• 其余10个月份可直接复制上述逻辑，仅修改月份数字和字段名称即可。

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