基于其他列的If语句

使用 case expression :

;With cte as 
(
select DATEDIFF(day,[Contract Start Date],[Contract End Date]) as contract_time,
a,
b,
c
from table 1
)

select contract_time
       case when contract_time is null then c
            when contract_time >= 390 then a
            when contract_time <  390 then b
       end as otherColumn
from cte

请注意,a、b和c必须都是相同的数据类型。

使用SQL Server 2012中支持的IIF函数,否则也可以使用CASE WHEN…THEN…END

;With cte_table1 as 
(
   SELECT 
       DATEDIFF(day,[Contract Start Date],[Contract End Date]) as contract_time,
       A,
       B,
       C
   FROM [table 1]
)

SELECT contract_time,
       IIF(contract_time is null,C,
           IIF(contract_time >= 390, A,B))
       as otherColumn
FROM cte_table1

试着这样,

SELECT DATEDIFF(day, [Contract Start Date], [Contract End Date]) AS contract_time
    ,CASE 
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) IS NULL
            THEN c
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) >= 390
            THEN a
        WHEN DATEDIFF(day, [Contract Start Date], [Contract End Date]) < 390
            THEN b
        END AS otherColumn
FROM TABLE