含CASE表达式变量声明的动态日期视图创建问题

因为SQL视图不允许声明局部变量，我们可以直接把原来@this_qtr_month的计算逻辑内联到Is QTD的判断语句中，替代变量的使用。下面是两种可行的修改方案：

方案1：直接内联变量逻辑 #

CREATE VIEW Date_Table_With_Periods AS
SELECT 
    *,
    DATEDIFF(day, [Date], GETDATE()) AS 'Days Aged',
    DATEDIFF(ww, [Date], GETDATE()) AS 'Weeks Aged',
    DATEDIFF(qq, [Date], GETDATE()) AS 'QTRs Aged',
    DATEDIFF(yy, [Date], GETDATE()) AS 'Years Aged',
    
    CASE WHEN DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN 'Y' ELSE 'N' END AS 'Is MTD',
    
    CASE 
        -- 把原变量的计算逻辑直接嵌入判断条件
        WHEN (CASE WHEN MONTH(GETDATE()) IN (1,4,7,10) THEN 1 
                   WHEN MONTH(GETDATE()) IN (2,5,8,11) THEN 2
                   WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 3
              END) > [Num of Month in QTR] THEN 'Y'
        WHEN (CASE WHEN MONTH(GETDATE()) IN (1,4,7,10) THEN 1 
                   WHEN MONTH(GETDATE()) IN (2,5,8,11) THEN 2
                   WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 3
              END) = [Num of Month in QTR] AND DATEPART(dd, [Date]) <= DATEPART(dd, GETDATE()) THEN 'Y'
        ELSE 'N'
    END AS 'Is QTD',
    
    CASE WHEN GETDATE() >= DATEFROMPARTS(YEAR(GETDATE()), [Month Num], DAY(date_modified_LeapYear)) THEN 'Y' ELSE 'N' END AS 'Is YTD'
FROM Date_Table_Static

方案2：用CTE简化重复逻辑 #

如果觉得重复写两次case冗余，可以用CTE先计算当前季度月份，后续直接引用：

CREATE VIEW Date_Table_With_Periods AS
WITH CurrentQtrInfo AS (
    SELECT 
        CASE WHEN MONTH(GETDATE()) IN (1,4,7,10) THEN 1 
             WHEN MONTH(GETDATE()) IN (2,5,8,11) THEN 2
             WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 3
        END AS this_qtr_month
)
SELECT 
    dt.*,
    DATEDIFF(day, dt.[Date], GETDATE()) AS 'Days Aged',
    DATEDIFF(ww, dt.[Date], GETDATE()) AS 'Weeks Aged',
    DATEDIFF(qq, dt.[Date], GETDATE()) AS 'QTRs Aged',
    DATEDIFF(yy, dt.[Date], GETDATE()) AS 'Years Aged',
    
    CASE WHEN DATEPART(dd, dt.[Date]) <= DATEPART(dd, GETDATE()) THEN 'Y' ELSE 'N' END AS 'Is MTD',
    
    CASE 
        WHEN cqi.this_qtr_month > dt.[Num of Month in QTR] THEN 'Y'
        WHEN cqi.this_qtr_month = dt.[Num of Month in QTR] AND DATEPART(dd, dt.[Date]) <= DATEPART(dd, GETDATE()) THEN 'Y'
        ELSE 'N'
    END AS 'Is QTD',
    
    CASE WHEN GETDATE() >= DATEFROMPARTS(YEAR(GETDATE()), dt.[Month Num], DAY(dt.date_modified_LeapYear)) THEN 'Y' ELSE 'N' END AS 'Is YTD'
FROM Date_Table_Static dt
CROSS JOIN CurrentQtrInfo cqi

核心思路 #

原变量@this_qtr_month的作用是根据当前系统日期的月份，算出它在所属季度里的序号（1=季度首月，2=次月，3=季末月）。我们把这段计算逻辑直接嵌入判断或用CTE提前计算，就能绕过视图不能声明变量的限制，同时保留原有业务逻辑。

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