I have the below SQL logic made up of three where statements each seperated by "OR". My goal is to retrieve all data within the first where statement, but exclude only those records that meet the criteria in the second two where statements. When I run it as is I get all the records. I think it's because of the first where statement, but I can't figure out how to change it so it only excludes the records that meet the criteria in the last two where statements. Any help you can provide to rewrite this logic is greatly appreciated.

WHERE 
(
    (ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN')
OR
    (o.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 NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
OR
    (o.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 ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
)

I have the below SQL logic made up of three where statements each seperated by "OR". My goal is to retrieve all data within the first where statement, but exclude only those records that meet the criteria in the second two where statements. When I run it as is I get all the records. I think it's because of the first where statement, but I can't figure out how to change it so it only excludes the records that meet the criteria in the last two where statements. Any help you can provide to rewrite this logic is greatly appreciated.

WHERE 
(
    (ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN')
OR
    (o.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 NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
OR
    (o.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 ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
)

• sql-server
• t-sql
• logic

• Could this perhaps help you? sql query with multiple where statements – Aixi Commented Nov 18, 2024 at 20:44
• 1 Please post sample data and desired results. – Barmar Commented Nov 18, 2024 at 20:52
• 1 @Barmar - not for inclusive start date and exclusive end date. The >= and < date range check is best practice. – T N Commented Nov 18, 2024 at 20:55
• 1 Please provide a minimal reproducible example with sample data and desired results. – Dale K Commented Nov 18, 2024 at 20:56
• 2 (repost) Do you perhaps need WHERE (conditions 1) AND NOT (conditions 2) AND NOT (conditions 3) or equivalently WHERE (conditions 1) AND NOT (conditions 2 OR conditions 3)? If so, you can also trim the second and third conditions down to just check the o.ord_revtype4 and s.cmp_id values. – T N Commented Nov 18, 2024 at 20:58

2 Answers 2

To "retrieve all data within the first [condition], but exclude ... those records that meet the criteria in the second two [conditions]", try:

WHERE (
    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 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')
)

(Redundant conditions have been trimmed.)

Or equivalently:

WHERE (
    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 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')
    )
)

This method is not the most efficient, but I often prefer it when excluding data. It allows you to identify exactly which records are being excluded by simply running the subquery alone. You can then check that it's excluding what you want it to, and not excluding things you don't;

SELECT * FROM [TABLE]

WHERE 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 [PRIMARYKEY] NOT IN (SELECT [PRIMARYKEY] FROM [TABLE]
                        WHERE o.ord_billto = 'CATGRI'
                        AND o.ord_status = 'CMP'
                        AND o.ord_invoicestatus = 'XIN'
                        AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
                        AND (

                            (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'))
                            )
                        )