CODEWITHSUNDEEP
sqlsql-server
Johnny Bones
Johnny Bones

Reputation: 8402

CASE statement in SQL Server

I'm having some trouble here. I'm not extremely fluent in SQL Server and I have this code in the WHERE clause of a SQL string:

[Get_Next_Status] = 'Closed' AND
(([Status] = 'No Error' AND [LiveAndPended] = 'Live' 
AND [Audit Date] Between @StDate And @EnDate)
OR (([Status] = 'No Error' AND [LiveAndPended] = 'Pended' 
AND CASE WHEN CP_DedicatedReleaserDate IS NULL THEN (
[Audit Date] Between @StDate And @EnDate) 
ELSE (CP_DedicatedReleaserDate Between @StDate And @EnDate) END)
OR ([Status] IN ('Financial','Processing') 
AND CP_DedicatedReleaserDate Between @StDate And @EnDate))

It doesn't seem to like that CASE statement. Can someone help me correct it?

Upvotes: 0

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

In SQL Server, conditions cannot be the result of a case. In this situation, you don't need case at all, coalesce() will work fine:

WHERE Get_Next_Status = 'Closed' AND
      (([Status] = 'No Error' AND [LiveAndPended] = 'Live' AND
        [Audit Date] Between @StDate And @EnDate
       ) OR
       ([Status] = 'No Error' AND [LiveAndPended] = 'Pended' AND
        COALESCE(CP_DedicatedReleaserDate, [Audit Date]) Between @StDate And @EnDate
       ) OR
       ([Status] IN ('Financial','Processing') AND 
        CP_DedicatedReleaserDate Between @StDate And @EnDate)
      )

Upvotes: 2

Related Questions