July 26, 2019 at 7:59 pm
Hello All,
I have a requirement to filter a set of values from two columns with out excluding other values from those columns.
Here is the DDL and sample data.
create table #tsttable
(id int,
cid int,
ccode varchar(4))
insert into #tsttable
select 1234, 100, 'out'
union all
select 1234, 100, 'in1'
union all
select 1234, 100, 'outa'
union all
select 1234, 111, 'in2'
union all
select 5678, 100, 'in2'
union all
select 5678, 100, 'outa'
union all
select 5678, 111, 'cnty'
union all
select 5678, 100, 'in1'
union all
select 5678, 100, 'in3'
union all
select 9101, 111, 'stt'
union all
select 9101, 100, 'out'
I have come up with two solutions using case and sub-query, looking to see any other efficient way to do this with out sub-query.
select *
from #tsttable
where 1 = (case when cid in (100, 111) and ccode in('out', 'outa', 'stt', 'cnty') THEN 0 else 1 end)
select * from
(select *, case when cid in (100, 111) and ccode in('out', 'outa', 'stt', 'cnty') THEN 0 else 1 end ANC
from #tsttable)x
where x.ANC = 1
drop table #tsttable
Expected data set:
idcidccode
1234100in1
1234100in2
5678100in2
5678100in1
5678100in3
Thank you!
July 26, 2019 at 9:39 pm
The following code seems simpler and easier to understand to me.
SELECT *
FROM #tsttable AS t
WHERE t.cid NOT IN (100, 111)
OR t.ccode NOT IN ('out', 'outa', 'stt', 'cnty')
Using EXCEPT can also be a good option, but I don't think this is the right situation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 28, 2019 at 5:00 pm
Another option:
Select *
From #tsttable t
Where Not (t.cid In (100, 111) And t.ccode In ('out', 'outa', 'stt', 'cnty'));
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2019 at 1:01 pm
Thank you! Drew & Jeffrey.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply