I have got a business situation and want to calculate the values depending on different scenarios. Can you please help me with this. for simplification i am including the following SQL query and not the actual lengthy query
[code][/declare @table table (id int, statusr varchar(20), reason varchar(100))
insert into @table values (1,'a' ,'N') , (2,'b','N'), (3,'c' ,'N') ,(4,'d','D')
select COUNT(case when statusr IN ('a','b','c') then 1 else 0 end) as test1
,COUNT(case when statusr IN ('b','c') then 1 else 0 end) as test2 from @table where reason = 'N'code]
I am expecting test1 to return 3 and test2 to return 2 but both of them return same result. Where am i doing anything wrong?