T-SQL CASE Statement to determine what is compared-CodePudding

T-SQL 2018 coding/logic question. I have CASE in a SELECT statement. Below I have provided the "pseudo code" of what the requirement is, but I need assistance in how to write the last AND of the CASE statement. Is it possible to change which condition is used inside a CASE statement based on values from other fields?

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

CodePudding user response：

Maybe something like this? Just add a second WHEN and include that condition IF ReceivedDate IS (NOT) NULL into the WHEN conditions for both - once with IS NOT NULL, once with 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

CodePudding user response：

It can be done this way:

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

Or to simplify even more, you can do the below which does NULL checking first.

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