Self Join

[dzdncnfsd]

I have a transaction table that contains both payments and payment reversals. I want to find specific transactions for each customer that cancel each other out. The "amount" field would be positive for payments, negative for reversals. How do I set up a self join on one table? Or is that what I should be doing?
Thanks,
Gladys

Gladys Clemmer
gladys.clemmer@fifsg.com

 

[rgbean]

Gladys,
A self join can be done, but it would be helpful to know what other useful fields you have defined in the transaction table (Invoice Number, Customer ID, Transaction Date, Payment Type, etc.) other than Payment Amount.

Rick

 

[ThomC]

Gladys,

Assuming you have a table with these types of fields:

CREATE TABLE Xact ;
(PKey I, ;
CustId I, ;
XDate D, ;
XAmt N(10, 2))

Where PKey is a legitimate primekey, CustId identifies the customer, XDate is the transaction date and XAmt is the transaction amount, your basic query is going to look like:

SELECT ;
XPay.PKey AS ChgKey, XRev.PKey AS RevKey ;
FROM ;
Xact XPay, Xact.XRev ;
WHERE ;
XPay.CustId = XRev.CustId ;
AND XPay.XAmt = XRev.XAmt ;
AND XPay.XDate >= XRev.XDate ;
AND XPay.PKey # XRev.PKey ;
INTO ;
CURSOR Reversals

This gets you the a set of primekey pairs for reversal candidates. The key word here is candidate.
There are a number of issues involved in reversal processing:

Its possible that a given transaction can have several reversal transactions that qualify.
Conversely, its possible that a reversal transaction can several candidate transactions.

I would do something like:

SELECT XPay.PKey AS ChgKey, XRev.PKey AS RevKey, XRev.XDate - XPay.XDate AS DeltaDate ;
FROM Xact XPay, Xact.XRev ;
WHERE XPay.CustId = XRev.CustId ;
AND XPay.XAmt = XRev.XAmt ;
AND XPay.XDate >= XRev.XDate ;
AND XPay.PKey # XRev.PKey ;
INTO CURSOR Revs1

SELECT A.* ;
FROM Revs1 A ;
WHERE A.DeltaDate = ;
(SELECT MIN(DeltaDate) ;
FROM Revs1 B ;
WHERE B.ChgKey = A.ChgKey ;
AND B.RevKey = A.RevKey) ;
GROUP BY A.ChgKey, A.RevKey ;
INTO CURSOR UniqRevs

DELETE ;
FROM Xact X ;
WHERE X.PKey IN ;
(SELECT ChgKey ;
FROM UniqRevs) ;
OR ;
X.PKey IN ;
(SELECT RevKey ;
FROM UniqRevs)

The idea being:
1) Get a set of transaction-reversal candidate pairs, along with the duration between the transaction and reversal.
2) Get unique transaction-reversal pairs, based on the shortest duration... the GROUP BY clause is a hack
to ensure unique reversal pairs. IT MAY BE PROBLEMATIC.
3) Delete the reversal pairs from the transaction table.

Regards,
Thom C.