如何实现基于当前月份自动匹配对应目标GP值的SQL查询？

你当前的代码已定义全年各月的目标变量，但需手动指定显示的月份目标（比如@MAY），要实现自动匹配当前月份，以下两种方案可满足需求：

方案一：用CASE语句自动匹配当前月份 #

通过MONTH(@Today)获取当前月份数字，结合CASE语句返回对应目标值，无需每月手动修改SELECT语句：

DECLARE @Today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME)
DECLARE @StartOfCurrentMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH,0,@Today), 0)
DECLARE @End DATETIME = @Today
DECLARE @JAN DEC(18,2) = 100000.50
DECLARE @FEB DEC(18,2) = 100000.51
DECLARE @MAR DEC(18,2) = 100000.52
DECLARE @APR DEC(18,2) = 100000.53 
DECLARE @MAY DEC(18,2) = 100000.54
DECLARE @JUN DEC(18,2) = 100000.55 
DECLARE @JUL DEC(18,2) = 100000.56 
DECLARE @AUG DEC(18,2) = 100000.57
DECLARE @SEP DEC(18,2) = 100000.58
DECLARE @OCT DEC(18,2) = 100000.59
DECLARE @NOV DEC(18,2) = 100000.60
DECLARE @DEC DEC(18,2) = 100000.61  

SELECT 
    SUM(OINV.GrosProfit) AS 'Current GP',
    CASE MONTH(@Today)
        WHEN 1 THEN @JAN
        WHEN 2 THEN @FEB
        WHEN 3 THEN @MAR
        WHEN 4 THEN @APR
        WHEN 5 THEN @MAY
        WHEN 6 THEN @JUN
        WHEN 7 THEN @JUL
        WHEN 8 THEN @AUG
        WHEN 9 THEN @SEP
        WHEN 10 THEN @OCT
        WHEN 11 THEN @NOV
        WHEN 12 THEN @DEC
    END AS 'Goal GP'
FROM OINV 
WHERE OINV.DocDate BETWEEN @StartOfCurrentMonth AND @End

MONTH(@Today)会返回1-12的月份数值，CASE语句自动匹配对应的目标变量，每月运行时无需调整代码。

方案二：用目标表存储（更易维护） #

若后续目标值需频繁修改，建议用临时表或永久表存储全年目标，通过关联查询匹配当前月份：

DECLARE @Today DATETIME = CAST(CAST(GETDATE() AS DATE) AS DATETIME)
DECLARE @StartOfCurrentMonth DATETIME = DATEADD(MONTH, DATEDIFF(MONTH,0,@Today), 0)
DECLARE @End DATETIME = @Today

-- 创建临时表存储全年目标
CREATE TABLE #MonthlyGoals (
    MonthNum INT,
    GoalGP DEC(18,2)
)

INSERT INTO #MonthlyGoals (MonthNum, GoalGP)
VALUES
(1, 100000.50),
(2, 100000.51),
(3, 100000.52),
(4, 100000.53),
(5, 100000.54),
(6, 100000.55),
(7, 100000.56),
(8, 100000.57),
(9, 100000.58),
(10, 100000.59),
(11, 100000.60),
(12, 100000.61)

SELECT 
    SUM(OINV.GrosProfit) AS 'Current GP',
    mg.GoalGP AS 'Goal GP'
FROM OINV
CROSS JOIN #MonthlyGoals mg
WHERE OINV.DocDate BETWEEN @StartOfCurrentMonth AND @End
AND mg.MonthNum = MONTH(@Today)
GROUP BY mg.GoalGP

DROP TABLE #MonthlyGoals

这种方式修改目标值只需更新INSERT语句中的数据，无需调整逻辑部分，适合目标值变动频繁的场景。

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