T-SQL CASE Statement to determine what is compared

Question

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

Answer 1

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