sql exclude offsetting rows

Question

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

Answer 1

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