sql-server - SQL Server - 带有 DATEDIFF 函数的 WHERE 子句中的 CASE WHEN

Question

我正在尝试使用 case when 和DATEDIFFfunction 执行下面的 where 语句。但我收到语法错误。请帮我。

WHERE
    -- primary contract is Flowing
    CTS.ContractStatusIdentifier = 'FLW'                                         
    AND (CASE 
            WHEN VMO.ProvinceOrStateCode = 'ON'
               THEN DATEDIFF (dd, CTR.RenewalDate, @currentdate) >= 75
               ELSE DATEDIFF (dd, CTR.RenewalDate, @currentdate) >= 45)
    AND VMO.ProvinceOrStateCode IN ('ON','AB')

标签： sql-serverwhere-clausedatediffcase-when

Answer 1

显然WHERE子句语法

AND (CASE 
        WHEN VMO.ProvinceOrStateCode = 'ON'
           THEN DATEDIFF (dd, CTR.RenewalDate, @currentdate) >= 75
           ELSE DATEDIFF (dd, CTR.RenewalDate, @currentdate) >= 45)

是无效的。为了简单，很简单，请尝试

AND DATEDIFF (dd, CTR.RenewalDate, @currentdate)
    >= (CASE 
           WHEN VMO.ProvinceOrStateCode = 'ON'
               THEN 75
               ELSE 45
        END)

祝你好运