sql-servert-sqllogic

Work with multiple where statements with different conditions


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

Solution

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