I am trying to figure out to exclude all records when the net equals $0
How to determine net $0:
1. Invoice numbers are same
2. UnitPrice is Positive on one and
3. UnitPrice is Negative on the other (RETURN invoice type)
The View with the details show these as an example:
2016-06-18 00:00:00.000
|
LTS0004832 |
1620287 |
Invoice |
DUES-LTSMB |
LTS-NMEM |
12.00000 |
2016-07-07 00:00:00.000 |
LTS0004832 |
1620287 |
Return |
DUES-LTSMB |
LTS-NMEM |
-12.00000 |
2016-08-03 00:00:00.000 |
LTS0004883 |
1624784 |
Return |
DUES-LTSMB |
LTS-NMEM |
-12.00000 |
2016-07-06 00:00:00.000 |
LTS0004962 |
2870 |
Invoice |
DUES-LTSMB |
LTS-PRGM |
50.00000 |
|
The ones in red cancels each other out. Net $0
Want to exclude those records in the result.
Have this but keep timing out (uses the View the details from the above is resulting)
Code:
SELECT Invoice, PaymentDate, MbrshipNo, ItemDescr, InvType, ClassID, ItemNo, ExtPrice, CustNo, CustName, OrgMbrNo
FROM dbo.AE_ExceptionInvoiceReport
WHERE (Invoice NOT IN
(SELECT Invoice
FROM dbo.AE_ExceptionInvoiceReport AS tmp
GROUP BY Invoice
HAVING (COUNT(*) > 1) AND (SUM(ExtPrice) <> 0)))