CODEWITHSUNDEEP
sqlsql-server-2012
Shoaib Maroof
Shoaib Maroof

Reputation: 369

Filtering on CASE Statement on WHERE clause including variable

I would like to filter out 'TR' records for when @BU = 'ES'. This works perfectly fine for 'ES' but when I run this query for another country, i.e. 'SE' it does not return the row count I expect. expected row count: 28941 row count returned: 28916

I know this is to do with the filer of 'TR'. How do I get this to work as it is for Spain and also when I run it for other BUs.

'TR' only needs to be filtered out for 'ES' only.

declare @BU varchar(2);
set @BU = 'SE';

select case
           when @BU = 'ES' then
               p.PanelClass
           else
               'b'
       end as L1
from Panels                  p
    left outer join Slots    sl
        on sl.ID = p.ID
    left outer join Packages pa
        on pa.ID = p.ID
where p.Status = 1
      and case
              when @BU = 'ES' then
                  p.PanelClass
              else
                  'b'
          end <> 'TR';

Upvotes: 0

Views: 421

Answers (1)

KMarron
KMarron

Reputation: 513

Try 

declare @BU varchar(2);
set @BU = 'SE';

select case
           when @BU = 'ES' then
               p.PanelClass
           else
               'b'
       end as L1
from Panels                  p
    left outer join Slots    sl
        on sl.ID = p.ID
    left outer join Packages pa
        on pa.ID = p.ID
where p.Status = 1
      and 'TR' <> case when @BU = 'ES' then p.PanelClass else 'b' end;

Upvotes: 1

Related Questions