在 CASE WHEN 语句中需要 SQL 别名列名

由于逻辑查询处理顺序，无法在同一块中引用NewDate列。为此，您可以使用公用表表达式，如下所示：

WITH Base_CTE
AS
(
SELECT CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate], date_nedded
FROM tablename
)
SELECT CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]
FROM Base_CTE; 

SELECT
CASE 
    WHEN CASE
           WHEN p.Program IN ('program1') THEN (date_needed + 10)
           WHEN p.Program IN ('program2') THEN (date_needed + 20)
         END < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]

或者使用子查询或 CTE...

WITH CTE AS(
SELECT
CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate], date_needed
FROM YourTable)
SELECT
CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]
FROM CTE

select p.*, (case when p.date_needed < p1.NewDate 
                  then 'Program On Time' 
                  else 'Program Late' 
              end) as [Modified New Date]
from table p cross apply
     ( values (  case when p.Program = 'program1' 
                      then (date_needed + 10)
                      when p.Program = 'program2' 
                      then (date_needed + 20)
                  end
              )
     ) p1(NewDate);

SELECT v.date_needed,
       (CASE WHEN v.date_needed < [NewDate] THEN 'Program On Time' 
             ELSE 'Program Late'
        END) AS [Modified New Date]
FROM t CROSS APPLY
     (VALUES (CASE WHEN t.Program IN ('program1') THEN (date_needed + 10)
                   WHEN t.Program IN ('program2') THEN (date_needed + 20)
              END)
     ) v(date_needed)