CODEWITHSUNDEEP
sqlsql-servercase
tablecloth26
tablecloth26

Reputation: 49

Not like statement in SQL not working

I'm trying to filter out those that are 'not interested' in the first case statement but it keeps assigning those that are 'not interested' the value 1:

SELECT
U.*,
L.ID AS Lead_ID,

CASE

WHEN ((L.Age_Change < DATEADD(day,-180, GETDATE())) OR (L.Age_Change IS NULL 
AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' OR
L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term NOT LIKE '%September 2019%' OR  
L.Start_Term NOT LIKE '%September 2020%' OR 
L.Start_Term NOT LIKE '%September 2021%')) THEN '1' 

WHEN (L._Stage IS NULL AND L.Created_Date < DATEADD(day,-45, GETDATE()) AND 
(L.Start_Term NOT LIKE '%September 2019%' OR 
L.Start_Term NOT LIKE '%September 2020%' OR 
L.Start_Term NOT LIKE '%September 2021%')) THEN '2'

WHEN ((L.Age_Last__Stage_Change < DATEADD(day,-180, GETDATE())) OR 
(L.Age_Last__Stage_Change IS NULL AND L._Stage IS NOT NULL) AND
(L._Stage NOT LIKE '%Not Interested%' OR
 L._Stage NOT LIKE '%Duplicate%') AND
(L.Start_Term LIKE '%September 2019%' OR 
 L.Start_Term LIKE '%September 2020%' OR 
 L.Start_Term LIKE '%September 2021%')) THEN '3'
 ELSE NULL END AS Case_Logic

 FROM
 TABLE_A AS U

I've used <> and != but both do not work. Any guidance on the issue would be greatly appreciated

Upvotes: 0

Views: 3099

Answers (2)

Andrew
Andrew

Reputation: 1596

(L._Stage NOT LIKE '%Not Interested%' OR L._Stage NOT LIKE '%Duplicate%')

This is likely to be always true, unless your have a _stage of 'Not Interested, Duplicate'. When you invert the LIKE you also have to invert your AND/OR logic, so your logic is the opposite of:

(L._Stage LIKE '%Not Interested%' AND L._Stage LIKE '%Duplicate%')

You have the same issue with your start terms. OR combined with NOT will match unless all three of your start terms are in the same column, based on the column name I expect it to only be one, a sort of start date, meaning it will match everything.

So what you really want is probably:

WHEN ((L.Age_Change < DATEADD(day,-180, GETDATE())) 
OR (L.Age_Change IS NULL 
  AND L._Stage IS NOT NULL)
AND (L._Stage NOT LIKE '%Not Interested%'
  AND L._Stage NOT LIKE '%Duplicate%')
AND (L.Start_Term NOT LIKE '%September 2019%'
  AND L.Start_Term NOT LIKE '%September 2020%' 
  AND L.Start_Term NOT LIKE '%September 2021%'))
THEN '1'

There is likely even more issues with your bracketing, including a lot of redundant parentheses, depending on where you want that OR for l.AgeChange to apply, you want either:

WHEN (L.Age_Change < DATEADD(day,-180, GETDATE() 
  OR L.Age_Change IS NULL) 
AND L._Stage IS NOT NULL
AND L._Stage NOT LIKE '%Not Interested%'
AND L._Stage NOT LIKE '%Duplicate%'
AND L.Start_Term NOT LIKE '%September 2019%'
AND L.Start_Term NOT LIKE '%September 2020%' 
AND L.Start_Term NOT LIKE '%September 2021%'
THEN '1'

To require the age change to be over 180 days or null and all other conditions still matter or:

WHEN (L.Age_Change < DATEADD(day,-180, GETDATE()) 
OR (L.Age_Change IS NULL 
  AND L._Stage IS NOT NULL
  AND L._Stage NOT LIKE '%Not Interested%'
  AND L._Stage NOT LIKE '%Duplicate%'
  AND L.Start_Term NOT LIKE '%September 2019%'
  AND L.Start_Term NOT LIKE '%September 2020%' 
  AND L.Start_Term NOT LIKE '%September 2021%')
THEN '1'

If age change is >180 days always 1, otherwise if the age change is null check the other conditions.

Upvotes: 1

twinklehawk
twinklehawk

Reputation: 639

'Not Interested' will always match your first case since it matches L._Stage NOT LIKE '%Duplicate%' and you used OR. Try changing it to L._Stage NOT LIKE '%Not Interested%' AND L._Stage NOT LIKE '%Duplicate%'

Upvotes: 1

Related Questions