CODEWITHSUNDEEP
sqlsql-server
Jordan Gilbertson
Jordan Gilbertson

Reputation: 31

sql exclude offsetting rows

In the query below, I would like to exclude any rows (vendor invoices) if there is another row with the same job ID and an opposite invoice total. For example, if job 1234 has vendor invoices in the amounts of -10, 10, and 20, then only the one with a value of 20 should be returned in the query results. Ideally, if a job has invoices in the amounts of -10, 10, 10, and 20, then the results should return the 20 invoice and the 10 invoice with the oldest invoice date. 

SELECT  J.JobID,
   VI.VendorInvoiceNo,
   VI.invoicetotal,
   VI.importedDate,
   VI.CreationDate,
   VI.InvoiceDate

FROM  VendorInvoices AS VI
   LEFT JOIN Jobs AS J ON J.JobID = VI.JobID

WHERE  J.operCompleteDate >= (GETDATE()-90)
   AND VI.invoicetotal IS NOT NULL
   AND VI.invoicetotal <> 0

Upvotes: 3

Views: 75

Answers (2)

Laughing Vergil
Laughing Vergil

Reputation: 3756

No need to use a CTE for this. That just adds overhead. Simply refer to the table twice in the query.

SELECT  
    J.JobID,
    VI.VendorInvoiceNo,
    VI.invoicetotal,
    VI.importedDate,
    VI.CreationDate,
    VI.InvoiceDate

FROM  VendorInvoices AS VI
INNER JOIN Jobs AS J        -- Since operCompleteDate must exist, use INNER JOIN
    ON J.JobID = VI.JobID
LEFT JOIN VendorInvoices filter
    ON filter.JobID = VI.JobID
    AND filter.InvoiceTotal = -(VI.InvoiceTotal)
WHERE  J.operCompleteDate >= (GETDATE()-90)
   AND VI.invoicetotal IS NOT NULL
   AND filter.invoiceTotal IS NOT NULL
   AND VI.invoicetotal <> 0

An index on JobID and InvoiceTotal will ensure maximum processing speed, but may not be necessary.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270483

Your query is basically:

SELECT vi.*
FROM VendorInvoices vi LEFT JOIN
     Jobs J
     ON J.JobID = VI.JobID
WHERE J.operCompleteDate >= (GETDATE()-90) AND
      vi.invoicetotal IS NOT NULL
      vi.invoicetotal <> 0;

If we use this as a CTE:

WITH vi as (
      SELECT vi.*
      FROM VendorInvoices vi LEFT JOIN
           Jobs J
           ON J.JobID = VI.JobID
      WHERE J.operCompleteDate >= (GETDATE()-90) AND
            vi.invoicetotal IS NOT NULL
            vi.invoicetotal <> 0
     )
SELECT vi.*
FROM vi
WHERE NOT EXISTS (select 1
                  from vi vi2
                  where vi2.jobid = vi.jobid and
                        vi2.invoicetotal = - vi.invoicetotal
                 );

Upvotes: 3

Related Questions