Working with multiple WHERE statements

Transact-SQL
1

Below is a subset of a much larger query. I am trying to create a query where by I get results that include the first condition in the WHERE statement below, but exclude the results that meet the last two conditions (GRIFF1 & GRIFF3). I thought the way this was written would work, but I am still getting the results I want to exclude. Any help anyone can provide is appreciated.

--DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = '10/29/2023' SET @EndDate = '11/3/2023' DECLARE @BillTo as varchar(max) SET @BillTo = 'CATGRI'
--DECLARE @StdRate as money SET @StdRate = 42.00 DECLARE @OTRate as money SET @OTRate = 42.00

SELECT
s.stp_number as StopNumberPre
, o.ord_hdrnumber as OrderNumberPre
, s.stp_sequence as SeqPre
, CONVERT(varchar,o.ord_startdate,101) as StartDatePre
, s.stp_arrivaldate as ArrivalDatePre
, o.ord_driver1 as DriverPre
into #pre1
FROM orderheader o (NOLOCK)
INNER JOIN stops s (NOLOCK) ON s.ord_hdrnumber = o.ord_hdrnumber
WHERE (
** ord_billto = @BillTo **
** AND o.ord_status = 'CMP'**
** AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1**
** AND o.ord_invoicestatus = 'XIN'**
)
AND NOT (
** o.ord_revtype4 IN ('GRIFF3')**
** AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')**
)
AND NOT (
** o.ord_revtype4 IN ('GRIFF1')**
** AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')**
)

2 
WHERE 
(
        ord_billto = @BillTo 
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
    (   o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
    )
    OR
    (   o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
    )
)
2 Likes
3

What, specifically, is begin included that you didn't expect?

Is it possible a column(s) in the WHERE clause have NULL value(s)? NULL will never be IN or NOT IN any value.

4

I am fairly new to SQL and learning the various operators and how they work together so this helps. Does the same concept apply if you have three statements with a mix of parameters with each condition?

WHERE
(
(ord_billto = @BillTo
AND o.ord_status = 'CMP'
AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
AND o.ord_invoicestatus = 'XIN'
AND o.ord_revtype4 NOT IN ('SAN1','SAN2','SAN3','SAN4','SAN5','SAN6','SAN7','SAN8','SAN9','SAN10','ATHWIL','MUNC1')
AND s.cmp_id <> 'SC2BAR')
OR
(ord_billto = 'CATATH'
AND o.ord_status = 'CMP'
AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
AND o.ord_invoicestatus = 'XIN'
AND o.ord_revtype4 IN ('ATHWIL')
AND s.cmp_id NOT IN ('ATHATH10','TMFWIL01','TMFWIL','ATHATH11'))
OR
(ord_billto = 'CATGRI'
AND o.ord_status = 'CMP'
AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
AND o.ord_invoicestatus = 'XIN'
AND o.ord_revtype4 IN ('GRIFF3')
AND s.cmp_id IN ('CATLAF10','ORDBEL', 'CATGRI02', 'CATWAC01'))
)

5

No there are no null values in the fields in these conditions. They are required fields that have to be populated. In for some of these is not correct and should be equal (=). My ultimate goal is to to get results that meet the conditions in each of the conditions within each section of this logic.

6

Forum Etiquette: How to post data/code on a forum to get the best help – SQLServerCentral

1 Like
7

I'm not sure exactly what you're asking, but it sounds related to order of operations.

If you remember PEMDAS / BODMAS / BIDMAS from math class, then you remember that operations are evaluated in order of a defined precedence. What PEMDAS, etc. doesn't cover is logical operators like AND and OR. These have a precedence as well. Rather than try to remember the order ANDs and ORs will be evaluated and rely on future code maintainers to also know the order, we can use the highest precedence operator... parentheses... to wrap our expression so we enforce the order we intend the expressions to be evaluated in.

Any time you have an OR, make sure it is wrapped in parentheses.

What is the intent, here?

WHERE 
    abc = 123
OR  this <> that
AND xyz = 789

Parentheses can make a big difference.

WHERE 
    (   abc = 123
    OR  this <> that
    )
AND xyz = 789

WHERE 
    (   abc = 123
    OR  (   this <> that
        AND xyz = 789
        )
    )
8

I was never very good at math. My ultimate goal is to include those records in the first section (@BillTo) and exclude those in the last two sections of the where statement (GRIFF1 & GRIFF3). I have tried multiple different statements including what was suggested above (below) and still no luck. Nothing is being excluded as they should be.

WHERE
(
ord_billto = @BillTo
AND o.ord_status = 'CMP'
AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
( o.ord_revtype4 IN ('GRIFF3')
AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
)
OR
( o.ord_revtype4 IN ('GRIFF1')
AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
)
)

9

I suspect SQLServer is doing exactly what you tell it to. If you really want help take the time to post DDL, consumable test data, the results you want to get from the test data and the results you currently get from the test data.

1 Like
10

You can also use a TCE. I use it when the query becomes to complicated to understand and can be divided into multiple blocks.

;WITH Query1 AS
(
SELECT * FROM Table
), Query2 AS
(
SELECT * FROM Query1
WHERE Status='CMP'
), Query3 AS
(
SELECT * FROM Query2
WHERE InvoiceStatus='XIN'
)
SELECT * FROM Query3