Простой синтаксис SQL NOT LIKE с операторами AND, похоже, игнорируется

Question

Большое спасибо за помощь! Ничто, по-видимому, не работает здесь. Что я хочу сделать, это удалить строки с определенным значением в определенном столбце. Вот так:

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'

Все же все мои попытки НЕ тщетны, так как запрос все равно возвращает строки с ними. Что я тут не так сделал?

Весь запрос представлен ниже:

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

Вы не уверены в приоритете между AND и OR.

OR имеет более низкий приоритет, чем AND, поэтому ваш запрос возвращает любую строку, где e.disposition like '4'

Я написал длинное объяснение аналогичной проблемы в своем ответе на Требуется помощь с корректным SQL пару недель назад.

Answer 2

Есть OR прямо внизу, который, как я предполагаю, будет засасывать некоторые строки, даже если они имеют исключенные значения. Эффект без скобок (A AND B AND C ... AND Z) OR W, Возможно, вам нужно сгруппировать предикаты по-другому.

Пока мы на этом, вам не нужно LIKE здесь, так как вы ищете точные совпадения. Сделайте это вместо этого:

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

То же самое касается вещей в нижней части.

Answer 3

У вас есть проблема с брекетингом:

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

Это должно быть

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

или же

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

?

Answer 4

Как уже отмечали другие, приоритет для логических операторов в SQL следующий порядок.

• Не
• А также
• Или же

которая объясняет проблему, которую вы видели.

Еще одна вещь, которую я заметил в опубликованном вами коде, это то, что он содержит такие строки

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

Это может / должно быть написано без приведения от строки к int как

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

Но на самом деле я думаю, что вы могли бы избавиться от всего этого огромного выражения и заменить его на

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