如何获取当前月份前的实际记录及当月后的预测记录

当前查询结果 #

需求描述 #

上述为当前查询结果，对应的脚本如下。需要基于当前月份动态控制数据来源：当前月份及之前的记录取实际数据（@ActualRecords），当前月份之后的记录取预测数据（@ForecastRecords），原实现仅按年份硬切的思路无法满足需求。

原脚本 #

DECLARE @ActualRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
DECLARE @ForecastRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
INSERT INTO @ForecastRecords VALUES ('2022', 120,110,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2023', 110,100,90,80,70,60,50,40,30,20,10,120);
INSERT INTO @ForecastRecords VALUES ('2024', 110,100,90,80,70,60,50,40,30,20,10,10);
INSERT INTO @ForecastRecords VALUES ('2025', 100,90,80,70,60,50,40,30,20,10,10,10);
INSERT INTO @ForecastRecords VALUES ('2026', 130,120,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2027', 150,140,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ActualRecords VALUES ('2022', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2023', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2024', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2025', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2026', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2027', 0,0,0,0,0,0,0,0,0,0,0,0);
    
SELECT * FROM @ActualRecords A 
WHERE FiscalYear < 2023 
UNION
SELECT * FROM @ForecastRecords F
WHERE FiscalYear >= 2023

优化后的解决方案 #

核心思路是将列存储的月份数据转为行存储（UNPIVOT），逐个月份判断数据来源，最后再转回列存储（PIVOT），实现基于当前月份的动态切换：

DECLARE @ActualRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
DECLARE @ForecastRecords TABLE (
[FiscalYear] INT,
[Dec] NUMERIC(19,0),
[Jan] NUMERIC(19,0),
[Feb] NUMERIC(19,0),
[Mar] NUMERIC(19,0),
[Apr] NUMERIC(19,0),
[May] NUMERIC(19,0),
[Jun] NUMERIC(19,0),
[Jul] NUMERIC(19,0),
[Aug] NUMERIC(19,0),
[Sep] NUMERIC(19,0),
[Oct] NUMERIC(19,0),
[Nov] NUMERIC(19,0));
    
INSERT INTO @ForecastRecords VALUES ('2022', 120,110,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2023', 110,100,90,80,70,60,50,40,30,20,10,120);
INSERT INTO @ForecastRecords VALUES ('2024', 110,100,90,80,70,60,50,40,30,20,10,10);
INSERT INTO @ForecastRecords VALUES ('2025', 100,90,80,70,60,50,40,30,20,10,10,10);
INSERT INTO @ForecastRecords VALUES ('2026', 130,120,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ForecastRecords VALUES ('2027', 150,140,100,90,80,70,60,50,40,30,20,10);
INSERT INTO @ActualRecords VALUES ('2022', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2023', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2024', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2025', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2026', 0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO @ActualRecords VALUES ('2027', 0,0,0,0,0,0,0,0,0,0,0,0);

-- 获取当前日历月份，并映射到财政月顺序（Dec为财政年第1月，Jan第2月...Nov第12月）
DECLARE @CurrentCalendarMonth INT = MONTH(GETDATE());
DECLARE @CurrentFiscalMonth INT = CASE @CurrentCalendarMonth
    WHEN 12 THEN 1
    ELSE @CurrentCalendarMonth + 1
END;
DECLARE @CurrentYear INT = YEAR(GETDATE());

WITH ActualUnpivoted AS (
    -- 将实际记录的列转成行
    SELECT FiscalYear, MonthName, Value
    FROM @ActualRecords
    UNPIVOT (
        Value FOR MonthName IN ([Dec], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov])
    ) AS unpvt
),
ForecastUnpivoted AS (
    -- 将预测记录的列转成行
    SELECT FiscalYear, MonthName, Value
    FROM @ForecastRecords
    UNPIVOT (
        Value FOR MonthName IN ([Dec], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov])
    ) AS unpvt
),
MonthOrder AS (
    -- 定义每个月份对应的财政月序号
    SELECT 'Dec' AS MonthName, 1 AS FiscalMonth
    UNION ALL SELECT 'Jan', 2
    UNION ALL SELECT 'Feb', 3
    UNION ALL SELECT 'Mar', 4
    UNION ALL SELECT 'Apr', 5
    UNION ALL SELECT 'May', 6
    UNION ALL SELECT 'Jun', 7
    UNION ALL SELECT 'Jul', 8
    UNION ALL SELECT 'Aug', 9
    UNION ALL SELECT 'Sep', 10
    UNION ALL SELECT 'Oct', 11
    UNION ALL SELECT 'Nov', 12
),
CombinedData AS (
    -- 合并实际与预测数据，按规则选择数据源
    SELECT 
        a.FiscalYear,
        a.MonthName,
        CASE 
            -- 过去年份的所有月份取实际数据
            WHEN a.FiscalYear < @CurrentYear THEN a.Value
            -- 当前年份中，当前财政月及之前的取实际，之后的取预测
            WHEN a.FiscalYear = @CurrentYear AND mo.FiscalMonth <= @CurrentFiscalMonth THEN a.Value
            -- 未来年份的所有月份取预测数据
            ELSE f.Value
        END AS FinalValue
    FROM ActualUnpivoted a
    JOIN ForecastUnpivoted f ON a.FiscalYear = f.FiscalYear AND a.MonthName = f.MonthName
    JOIN MonthOrder mo ON a.MonthName = mo.MonthName
)
-- 将行数据转回列格式，保持原输出结构
SELECT FiscalYear, [Dec], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov]
FROM CombinedData
PIVOT (
    MAX(FinalValue) FOR MonthName IN ([Dec], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov])
) AS pvt
ORDER BY FiscalYear;

关键说明 #

1. 行转列/列转行：通过UNPIVOT将每个月份的列数据拆分为单独行，便于按月份判断数据源；最终用PIVOT转回原列格式，保证输出结构一致。
2. 动态月份判断：通过GETDATE()获取当前日期，映射到财政月顺序（适配你的财政年起始月，若财政年不是12月开头，只需调整MonthOrder和@CurrentFiscalMonth的计算逻辑）。
3. 数据源规则：
   • 过去年份的所有月份：取实际数据
   • 当前年份中，当前月份及之前：取实际数据
   • 当前年份中，当前月份之后：取预测数据
   • 未来年份的所有月份：取预测数据

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