CODEWITHSUNDEEP
sqlsql-server-2008
user2022883
user2022883

Reputation: 29

Finding rows matching positive and negative

I have 3 rows but I need to identify just the rows that offset, meaning the positive amount matches the negative amount, for the same vendor + invoice + check number combination.

Vendor_Code  Invoice_Number  Check_Number  Payment_Amount  Subcontract_Job
-----------  --------------  ------------  --------------  ---------------
DEVONEL      6462            35272             8257.50     BUP008
DEVONEL      6462            35272            -8257.50     BUP008
DEVONEL      6462            35665             8257.50     BUP008

Only the first two rows should be returned, since the third row doesn't have a matching negative offset.

Upvotes: 0

Views: 2072

Answers (2)

MarkWalls
MarkWalls

Reputation: 925

If the amounts are the same (there isn't a rounding difference or some kind of "it is close to" type issue with the amounts - if so think of rounding the amount field or adding/subtracting the margin from it.) then you could do a set based comparison like:

Select VendorCode, InvoiceNumber, CheckNumber, Amount from dbo.tablename
intersect
Select VendorCode, InvoiceNumber, CheckNumber, Amount from dbo.tablename

And it would give you the list of matching payments. If you just want to find non-matching records you could use Except instead of intersect. (Like if you did the above and passed it into an update incase you had a flag field to mark non-balanced payments in an invoicing system.)

I would use the above in a CTE and then feed that into an update.

Upvotes: 0

anon
anon

Reputation: 

SELECT x1.<cols>, x2.<cols> 
FROM dbo.YourTable AS x1
INNER JOIN dbo.YourTable AS x2
  ON x1.Vendor_Code    = x2.Vendor_Code
 AND x1.Invoice_Number = x2.Invoice_Number
 AND x1.Check_Number   = x2.Check_Number
 AND x1.Payment_Amount = -x2.Payment_Amount;

Upvotes: 3

Related Questions