Case in where clause?

Transact-SQL
1

I have this attempt to have a case in the where clause:
WHERE CASE WHEN sg.storecode = 'Y1' THEN sg.storecode
ELSE sg.storecode END = sg.storecode

I know it's not correct but I don't know how to code this. Basically what I want to do is if there are two records with storecode 'Y1' and 'S1', always get the record with the 'Y1'.

2

Here is a sample data:
number year level storecode
31125 2013 LP3 Y1
31125 2014 LP3 Y1
31125 2015 LP4 Y1
31125 2016 LP4 Y1
31125 2017 LP3 S1
31125 2017 LP4 Y1

I want to be able to filter these so that I only get all records with storecode='Y1'. The one records with 'S1' should not be returned.

3

You are probably going to need to be more clear, but based on what you said should just be simple Where clause

if OBJECT_ID('tempDb..#sample_data') is not null drop table #sample_data

create table #sample_data
(
number numeric,
[year] smallint,
[level] char(3),
storecode char(2)
)

insert #sample_data 
values
('31125','2013','LP3','Y1'),
('31125','2014','LP3','Y1'),
('31125','2015','LP4','Y1'),
('31125','2016','LP4','Y1'),
('31125','2017','LP3','S1'),
('31125','2017','LP4','Y1');


select  * 
from #sample_data 
where storecode = 'Y1';
4 
select [number]
      ,[year]
      ,[level]
      ,max(storecode) as storecode
  from yourtable
 group by [number]
         ,[year]
         ,[level]
5

Another approach:

select top(1) with ties
       [number]
      ,[year]
      ,[level]
      ,storecode
  from yourtable
 order by row_number() over(partition by [number]
                                        ,[year]
                                        ,[level]
                                order by storecode desc
                           )
1 Like