T-SQL CASE语句以确定比较的内容。

Question

T 2018编码/逻辑问题。我在一个CASE语句中包含了SELECT。下面我提供了需求的“伪代码”，但在如何编写最后一条和CASE语句方面需要帮助。是否可以根据其他字段的值更改CASE语句中使用的条件？

SELECT
    [DocumentNo],
    [DocumentType],
    CASE 
        WHEN [DocumentStatus] IS 'APPROVED'
             AND [DocumentBusiness] = 'COMMERCIAL' 
             AND DATEDIFF(Hours, [ReceivedDate], GETDATE()) < 5 but if                 
                 [ReceivedDate] is null, then use DateDiff(Hours,[ProcessDate], 
                 GETDATE()) < 10 instead.
            THEN 1 
            ELSE 0
    END AS 'DocumentPerformance'
FROM
    DocumentTbl01

Answer 1

可以这样做：

SELECT
  [DocumentNo],
  [DocumentType],
  CASE 
    WHEN [DocumentStatus] = 'APPROVED'
      AND [DocumentBusiness] = 'COMMERCIAL' 
      AND (
        (ReceivedDate IS NOT NULL AND DATEDIFF(Hours, [ReceivedDate], GETDATE()) < 5)
        OR
        (ReceivedDate IS NULL AND DATEDIFF(Hours, [ProcessDate], GETDATE()) < 10)
      )
      THEN 1 
    ELSE 0
  END AS 'DocumentPerformance'
FROM DocumentTbl01

或者，为了简化更多的操作，您可以执行下面的操作，首先执行空检查。

SELECT
  [DocumentNo],
  [DocumentType],
  IIF
  (
    [DocumentStatus] = 'APPROVED' 
    AND [DocumentBusiness] = 'COMMERCIAL' 
    AND 
    (
      (ReceivedDate IS NULL AND DATEDIFF(Hours, [ProcessDate], GETDATE()) < 10) 
      OR (DATEDIFF(Hours, [ReceivedDate], GETDATE()) < 5)
    )
    ,1
    ,0
  ) AS 'DocumentPerformance'
FROM DocumentTbl01

Answer 2

也许是这样的？只需添加第二个WHEN，并将该条件IF ReceivedDate IS (NOT) NULL包含到两个条件的WHEN条件中--一次使用IS NOT NULL，一次使用IS NULL。

SELECT
    [DocumentNo],
    [DocumentType],
    CASE 
        WHEN [DocumentStatus] = 'APPROVED'
               AND [DocumentBusiness] = 'COMMERCIAL' 
               AND ReceivedDate IS NOT NULL
               AND DATEDIFF(Hours, [ReceivedDate], GETDATE()) < 5 
            THEN 1 
        WHEN [DocumentStatus] = 'APPROVED'
               AND [DocumentBusiness] = 'COMMERCIAL' 
               AND ReceivedDate IS NULL
               AND DATEDIFF(Hours, [ProcessDate], GETDATE()) < 10
            THEN 1 
        ELSE 0
    END AS 'DocumentPerformance'
FROM
    DocumentTbl01

Answer 3

看来你可以直接用ISNULL

SELECT
    DocumentNo,
    DocumentType,
    CASE 
        WHEN DocumentStatus = 'APPROVED'
               AND DocumentBusiness = 'COMMERCIAL' 
               AND DATEDIFF(hour, ISNULL(ReceivedDate, ProcessDate), GETDATE()) < 10
            THEN 1 
        ELSE 0
    END AS DocumentPerformance
FROM
    DocumentTbl01;