CODEWITHSUNDEEP
sqlsql-servercase-expression
Mark Baxter
Mark Baxter

Reputation: 43

sql case in where clause

I've a report in SSRS and one of the parameters is based on whether a person attended an interview, failed to attend or no current feedback.

The SQL is as follows:

SELECT 
   (C.Forename + ' ' + C.Surname) as Fullname, 
   C.NINO, 
   S.SignpostingType, ST.StakeholderDesc, S.SignpostDate, 
   (CASE WHEN S.Attended = 0 THEN 'False' ELSE 'True' END) AS Attended, 
   (CASE WHEN S.FailedToAttend = 0 THEN 'False' ELSE 'True' END) AS FailedToAttend, 
   (CASE WHEN S.Experience = '.' THEN '' ELSE S.Experience END) AS Experience  
FROM 
    Customer C 
INNER JOIN
    Signposting S on S.CustomerID = C.CustomerID 
INNER JOIN 
    Stakeholder ST on ST.StakeholderID = S.StakeholderID 
WHERE 
    (S.SignpostDate >= '2001-01-01' And S.SignpostDate <= '2015-01-01') 
    AND (S.StakeholderID in (1,2,3,4,5,6,7,52,53,55,70,71,73)) 
    --AND (S.Experience in (@Experience))
    --AND (S.SignpostingType in (@SignpostType))
    AND
       CASE @Attended   
           WHEN 0 THEN (S.Attended = 1 AND S.FailedToAttend = 0)
           WHEN 1 THEN (S.Attended = 0 AND S.FailedToAttend = 1)
           WHEN 2 THEN (S.Attended = 0 AND S.FailedToAttend = 0)
       END

The problem is in the final AND condition. Depending on the value of @Attended I want the different column values to be used in my query.

I know I could do it dynamically in a stored procedure but I've been asked to write something that will have all the SQL in SSRS. I just want to make sure I'm exhausting my options before I push back on this.

Thanks in advance for your time and help.

Upvotes: 2

Views: 112

Answers (2)

Pavel Nikolov
Pavel Nikolov

Reputation: 9541

It can be done with a combination of AND and OR statements - no need for CASE here.

AND (
 (@Attended = 0 AND S.Attended = 1 AND S.FailedToAttend = 0) OR
 (@Attended = 1 AND S.Attended = 0 AND S.FailedToAttend = 1) OR
 (@Attended = 2 AND S.Attended = 0 AND S.FailedToAttend = 0) )

Upvotes: 4

Anon
Anon

Reputation: 10908

SELECT 
   (C.Forename + ' ' + C.Surname) as Fullname, 
   C.NINO, 
   S.SignpostingType, ST.StakeholderDesc, S.SignpostDate, 
   A.Attended, 
   A.FailedToAttend, 
   (CASE WHEN S.Experience = '.' THEN '' ELSE S.Experience END) AS Experience  
FROM 
    Customer C 
INNER JOIN
    Signposting S on S.CustomerID = C.CustomerID 
INNER JOIN 
    Stakeholder ST on ST.StakeholderID = S.StakeholderID
INNER JOIN
    (VALUES
       (0,'True' ,'False'),
       (1,'False','True' ),
       (2,'False','False')
    ) A(Code,Attended,FailedToAttend) on A.Code = @Attended
WHERE 
    (S.SignpostDate >= '2001-01-01' And S.SignpostDate <= '2015-01-01') 
    AND (S.StakeholderID in (1,2,3,4,5,6,7,52,53,55,70,71,73))

Upvotes: 0

Related Questions