SQL Where clause that exclude a value out of 4 columns

1

I need to create a query to exclude the value AD and NG out of my results.

My query that I created is not working. I still see NG in the file. I played around witht he or but still now working the way it should. I still see an NG in the clm_att1 field.

Select Distinct
clm_id1, clm_84b , clmx_50b3, clm_dout, clm_tchg, clm_nego, clm_sppo, clm_lnet, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5
from IMPACT.dbo.clm
left join impact.dbo.clmx on clmx_id1 = clm_id1
Where
clm_att5 = '!P' and clm_dout between '01/01/2018' and '12/31/2018' and
(clm_att1 NOT IN ('AD','NG') or clm_att2 NOT IN ('AD','NG') or clm_att3 NOT IN ('AD','NG') or clm_att4 NOT IN ('AD','NG') )

2

You need to use ANDs instead of ORs:

(clm_att1 NOT IN ('AD','NG') AND clm_att2 NOT IN ('AD','NG') AND clm_att3 NOT IN ('AD','NG') AND clm_att4 NOT IN ('AD','NG') )

1 Like
3

I tried that, but then it only gives me 2 results and that is it. I think I must need to do something with the clm_dout and clm_att5 fields.

**clm_dout between '01/01/2018' and '12/31/2018' and **
clm_att5 = '!p' and
(clm_att1 NOT IN ('AD','NG') AND clm_att2 NOT IN ('AD','NG') AND clm_att3 NOT IN ('AD','NG') AND clm_att4 NOT IN ('AD','NG') )

4

Do you have any DDL and sample data we could test with?

5

FROM IMPACT.dbo.clm
LEFT JOIN impact.dbo.clmx
ON clmx_id1 = clm_id1
WHERE clm_att5 = '!P'
AND clm_dout
BETWEEN '01/01/2018' AND '12/31/2018'
AND NOT (
clm_att1 IN ( 'AD', 'NG' )
OR clm_att2 IN ( 'AD', 'NG' )
OR clm_att3 IN ( 'AD', 'NG' )
OR clm_att4 IN ( 'AD', 'NG' )
);