如何获取跨年数据获取

[麻哩麻饭的]

数据源 表tYBCZDJ：
ID                FYear    FMonth    FYBCZYF(金额字段)

SHANGHAI  2022     11           443,431.19
SHANGHAI  2023     04           919,552.57
SHANGHAI  2023     05           1,400,920.44
SHANGHAI  2023     06           1,798,342.25
SHANGHAI  2023     12           50000
SHANGHAI  2024     01           100000

 现在报表格式为：
ID  本月  上月  本年  累计
如果不跨年没有问题，直接用case when 写，代码如下：
 

DECLARE @fyear VARCHAR(255);
DECLARE @fmonth VARCHAR(255);

SET @fyear='2023'
SET @fmonth='06'


SELECT ID,SUM(CASE WHEN FYear=@fyear AND FMonth=@fmonth THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END )本月,
						       SUM(CASE WHEN FYear=@fyear AND FMonth=@fmonth-1 THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END )上月,
						       SUM(CASE WHEN FYear=@fyear  THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END )本年,
							   SUM(TRY_PARSE(FYBCZYF AS NUMERIC(18,2)))累计
						    FROM tYBCZDJ WHERE ID='SHANGHAI' GROUP BY ID

但是2024年1月份查询应该是2023年12月份，这个在上月如何写的？请指教。

[麻哩麻饭的]

咋没人回复？

[麻哩麻饭的]

@日月于山河上 谢谢，非常有用！

[日月于山河上]

-- 声明并赋值变量
DECLARE @fyear VARCHAR(255);
DECLARE @fmonth VARCHAR(255);

SET @fyear='2024';
SET @fmonth='01';

-- 查询语句，计算汇总金额
SELECT ID,
SUM(CASE WHEN FYear=@fyear AND FMonth=@fmonth THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END ) AS 本月,
SUM(CASE WHEN FYear=CASE @fmonth WHEN '01' THEN CAST(@fyear AS INT) - 1 ELSE @fyear END
AND FMonth=CASE @fmonth WHEN '01' THEN '12' ELSE CAST(CAST(@fmonth AS INT) - 1 AS VARCHAR(2)) END
THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END ) AS 上月,
SUM(CASE WHEN FYear=@fyear THEN TRY_PARSE(FYBCZYF AS NUMERIC(18,2)) ELSE 0 END ) AS 本年,
SUM(TRY_PARSE(FYBCZYF AS NUMERIC(18,2))) AS 累计
FROM tYBCZDJ
WHERE ID='SHANGHAI'
GROUP BY ID;