Easy SQL Syntax NOT LIKE with AND operators seem to be ignored

Question

Thank you so much for helping! Nothing I seem to do works here. What I want to do is remove rows with a certain value in a certain column. Like so:

Where SegStart_Date between getdate()-90 and getdate()-1
    And q.Center not like 'Collections Center'
    And q.Center not like 'Cable Store'
    And q.Center not like 'Business Services Center'
    And q.Center not like 'Escalations'

Yet all my attempts to NOT are futile as the query returns rows with them anyway. What did I do wrong here?

The whole query is provided below:

Select segstart,
CASE
--when hour is = 0 and min < 30 then 0
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('0' as int)
--when hour is = 0 and min > 30 then 30
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('30' as int)
--when hour is 1 and min < 30 then 100
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('100' as int)
--when hour is 1 and min > 30 then 130
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('130' as int)
--when hour is 2 and min < 30 then 200
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('200' as int)
--when hour is 2 and min > 30 then 230
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('230' as int)
--when hour is 3 and min < 30 then 300
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('300' as int)
--when hour is 3 and min > 30 then 330
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('330' as int)
--when hour is 4 and min < 30 then 400
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('400' as int)
--when hour is 4 and min > 30 then 430
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('430' as int)
--when hour is 5 and min < 30 then 500
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('500' as int)
--when hour is 5 and min > 30 then 530
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('530' as int)
--when hour is 6 and min < 30 then 600
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('600' as int)
--when hour is 6 and min > 30 then 630
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('630' as int)
--when hour is 7 and min < 30 then 700
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('700' as int)
--when hour is 7 and min > 30 then 730
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('730' as int)
--when hour is 8 and min < 30 then 800
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('800' as int)
--when hour is 8 and min > 30 then 830
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('830' as int)
--when hour is 9 and min < 30 then 900
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('900' as int)
--when hour is 9 and min > 30 then 930
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('930' as int)
--when hour is 10 and min < 30 then 1000
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1000' as int)
--when hour is 10 and min > 30 then 1030
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1030' as int)
--when hour is 11 and min < 30 then 1100
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1100' as int)
--when hour is 11 and min > 30 then 1130
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1130' as int)
--when hour is 12 and min < 30 then 1200
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) < 30  THEN CAST('1200' as int)
--when hour is 12 and min < 30 then 1230
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1230' as int)
--when hour is 13 and min < 30 then 1300
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1300' as int)
--when hour is 13 and min > 30 then 1330
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1330' as int)
--when hour is 14 and min < 30 then 1400
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1400' as int)
--when hour is 14 and min > 30 then 1430
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1430' as int)
--when hour is 15 and min < 30 then 1500
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1500' as int)
--when hour is 15 and min > 30 then 1530
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1530' as int)
--when hour is 16 and min < 30 then 1600
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1600' as int)
--when hour is 16 and min > 30 then 1630
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1630' as int)
--when hour is 17 and min < 30 then 1700
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1700' as int)
--when hour is 17 and min > 30 then 1730
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1730' as int)
--when hour is 18 and min < 30 then 1800
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1800' as int)
--when hour is 18 and min > 30 then 1830
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1830' as int)
--when hour is 19 and min < 30 then 1900
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1900' as int)
--when hour is 19 and min > 30 then 1930
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('1930' as int)
--when hour is 20 and min < 30 then 2000
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2000' as int)
--when hour is 20 and min > 30 then 2030
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('2030' as int)
--when hour is 21 and min < 30 then 2100
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2100' as int)
--when hour is 21 and min > 30 then 2130
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('2130' as int)
--when hour is 22 and min < 30 then 2200
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2200' as int)
--when hour is 22 and min > 30 then 2230
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('2230' as int)
--when hour is 23 and min < 30 then 2300
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2300' as int)
--when hour is 23 and min > 30 then 2330
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) >= 30 THEN CAST('2330' as int)
ELSE Null End as Interval,
    FiscalMonthYear,
    SegStart_Date,
    dialed_num,
    callid,
    Name,
    t.Queue AS 'Xferto',
    TransferType,
    RepLName+', '+RepFName AS Agent,
    SupLName+', '+SupFName AS Sup,
    MgrLName+', '+MgrFName AS Manager,
    q.Center,
    q.Queue AS 'Xferfrom',
    e.anslogin,
    e.origlogin,
    t.Extension,
    transferred,
    disposition,
    talktime,
    dispsplit
From CMS_ECH.dbo.CaliforniaECH e

INNER JOIN Cal_RemReporting.dbo.TransferVDNs t on e.dialed_num = t.Extension
INNER JOIN InfoQuest.dbo.IQ_Employee_Profiles_v3_AvayaId q on e.origlogin = q.AvayaID
INNER JOIN Cal_RemReporting.dbo.udFiscalMonthTable f on e.SegStart_Date = f.Tdate

Where SegStart_Date between getdate()-90 and getdate()-1
    And q.Center not like 'Collections Center'
    And q.Center not like 'Cable Store'
    And q.Center not like 'Business Services Center'
    And q.Center not like 'Escalations'
    And SegStart_Date between RepToSup_StartDate and RepToSup_EndDate
    And SegStart_Date between SupToMgr_StartDate and SupToMgr_EndDate
    And SegStart_Date between Avaya_StartDate and Avaya_EndDate
    And SegStart_Date between RepQueue_StartDate and RepQueue_EndDate
    AND e.transferred like '1'
    OR e.disposition like '4' order by center

Answer 1

This sounds suspiciously like a table scan. Do you have an index that would make that query efficient? If there are few enough rows, you could probably make this into a subselect to avoid the table scan.

Answer 2

There is an OR right at the bottom, which I presume will suck in some rows even if they have the excluded values. The effect, without parentheses, is (A AND B AND C ... AND Z) OR W. Perhaps you need to group your predicates differently.

While we're at it, you don't need LIKE here, since you are looking for exact matches. Do this instead:

Where SegStart_Date between getdate()-90 and getdate()-1
    And q.Center not in ('Collections Center',
                         'Cable Store',
                         'Business Services Center',
                         'Escalations')

The same goes for the stuff towards the bottom.

Answer 3

You have a bracketing problem:

   AND e.transferred like '1'
    OR e.disposition like '4'

Is that supposed to be

   AND (e.transferred like '1'
    OR e.disposition like '4')

or

   (... 
   AND e.transferred like '1')
    OR e.disposition like '4'

?

Answer 4

You are confused about the precedence between AND and OR.

OR has lower precedence than AND, which makes your query return any row where e.disposition like '4'

I wrote a lengthy explanation for an equivalent problem in my answer to Need help with correct SQL a couple of weeks ago.

Answer 5

As others have pointed out the precedence for boolean operators in SQL is the following order.

• Not
• And
• Or

which explains the problem you were seeing.

Another thing I noticed in the code you posted is that it contains lines like

WHEN DATEPART(HOUR, segstart) = 0 
      AND DATEPART(MINUTE, segstart) < 30 THEN CAST('0' as int)

This could/should be written without the cast from string to int as

WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) < 30 THEN 0

But in fact I think you could get rid of that whole huge case expression and replace it with

100*DATEPART(HOUR, segstart) +30*(DATEPART(MINUTE, segstart)/30)  as Interval