CODEWITHSUNDEEP
sqlt-sql
David Yenglin
David Yenglin

Reputation: 685

Creating query with case statement in where clause

I have a large stored procedure that pulls data for a report. I am trying to use one of two different values in a WHERE clause depending on certain criteria. I cannot figure out how to use a CASE statement to get this done, I keep getting errors in the THEN part. Below is what I'm trying to do, it's a small portion of the larger stored procedure. So what I am looking for is where dlp(DATETIME) is newer than dlc(DATETIME) I want to use dlp to evaluate with the @AgeStart and @AgeEnd parameter, if not then use dlc to evaluate with @AgeStart and @AgeEnd.

@AgeStart INT
@AgeEnd INT

SET @Recovery = (
SELECT SUM(ISNULL(M.Paid1, 0))
FROM Master AS M
WHERE M.Status IN ('xxx','yyy')
  AND CASE
    WHEN COALESCE(M.dlp, '2000-01-01 00:00:00') > 
         COALESCE(M.dlc, '2000-01-01 00:00:00') 
    THEN DATEDIFF(dd,M.Received,M.dlp) 
      >= @AgeStart AND DATEDIFF(dd,M.Received,M.dlp) <= @AgeEnd
    ELSE DATEDIFF(dd, M.dlc, M.Received) 
      >= @AgeStart AND DATEDIFF(dd, M.dlc, M.Received) <= @AgeEnd
  END
  AND M.Balance >= @OrigBalanceMin AND M.Balance <= @OrigBalanceMax
)

Upvotes: 3

Views: 272

Answers (1)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

You don't need a case statement, you can just nest them in parentheses and use an OR conditional. I think I got all my parentheses lined up:

WHERE M.Status IN ('xxx','yyy')
    AND 
    /* Your updated case statement starts here */
    (
        (
            COALESCE(M.dlp, '2000-01-01 00:00:00') > 
                COALESCE(M.dlc, '2000-01-01 00:00:00') AND 
            DATEDIFF(dd,M.Received,M.dlp) >= @AgeStart AND 
                DATEDIFF(dd,M.Received,M.dlp) <= @AgeEnd
        ) OR (
            COALESCE(M.dlp, '2000-01-01 00:00:00') <= 
                COALESCE(M.dlc, '2000-01-01 00:00:00') AND 
            DATEDIFF(dd, M.dlc, M.Received) >= @AgeStart AND 
                DATEDIFF(dd, M.dlc, M.Received) <= @AgeEnd
        )
    )
    /* Your updated case statement ends here */
    AND M.Balance >= @OrigBalanceMin AND M.Balance <= @OrigBalanceMax
)

Upvotes: 1

Related Questions