|Companies can choose between two methods to reconcile charges with payments. The first method (known in Canada as Open Item) means that you match payments against charges exactly, typically by carrying the charge number in the payments table. The second method (known in Canada as Statement) means that you add up all the charges and all the payments, and then attempt to match them up, reporting the difference as the outstanding balance. The latter method is much simpler, so we will ignore it and concentrate on the first method.
The Open Item method rests on two assumptions: first, that the payments received will exactly match the amounts charged, and that the customer will write the charge number on the check. Both assumptions are shaky.
This can cause the staff responsible for account reconciliation considerable grief. Can we SQL programmers come to the rescue? Of course we can! It may not be 100% foolproof, but it will drastically cut down the onerous work of reconciliation.
Here is some code to create a schema and a pair of tables. Create a test database and then run this SQL:
Create Schema Loose GO CREATE TABLE [Loose].[Charges]( [PK] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Amount] [money] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [Loose].[Payments]( [PK] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Amount] [money] NOT NULL ) ON [PRIMARY] GONow populate the tables with a few rows of sample data, ensuring that you have a variety of payments â€“ some that match the charge exactly, some that are close but not enough, and some that are slight overpayments.
INSERT INTO Loose.Charges VALUES (100) INSERT INTO Loose.Charges VALUES (12) INSERT INTO Loose.Charges VALUES (56) INSERT INTO Loose.Charges VALUES (43) INSERT INTO Loose.Charges VALUES (59) INSERT INTO Loose.Charges VALUES (998) GO INSERT INTO Loose.Payments VALUES (99) INSERT INTO Loose.Payments VALUES (62) INSERT INTO Loose.Payments VALUES (40) INSERT INTO Loose.Payments VALUES (50) INSERT INTO Loose.Payments VALUES (12) INSERT INTO Loose.Payments VALUES (1000) GOThe first thing we need to do is define what I will call the proximity threshold: how close must the amount paid be to the amount charged before we conclude that the amounts are related? In the example below, I have defined the proximity threshold as 2. In a real-world example, it might be 10 or 50 or even 1000. It all depends on the nature of the business and the typical total purchase. A house builder may make frequent purchases valued at $1000 and more. You scale the threshold to the typical situation. Since the amount paid might be more or less or even (gasp) equal to the amount charged, to match a payment with a charge we must look for a range both below and above the amount charged. That might suggest a
DECLARE @proximity int SET @proximity = 2 -- change this value to suit your situation SELECT Loose.Charges.PK AS Charge#, Loose.Charges.Amount AS Charge, Loose.Payments.PK AS Payment#, Loose.Payments.Amount AS Payment FROM Loose.Charges INNER JOIN Loose.Payments ON ABS(Loose.Charges.Amount - Loose.Payments.Amount) <= @proximity GOBefore you run the code, anticipate what will result. Then run the code to see if you were right. Here is the result set:
Charge# Charge Payment# Payment 1 100.00 1 99.00 2 12.00 5 12.00 6 998.00 6 1000.00The solution is correct, as far as it goes, but it doesnâ€™t go very far. We have correctly identified the three situations: underpayment, exact payment and overpayment, but this approach suppresses all the charges that donâ€™t have a matching payment, and no doubt the reconciliation staff are interested in a bigger picture of the situation. We can fix this by changing the join:
DECLARE @proximity int SET @proximity = 2 SELECT Loose.Charges.PK AS Charge#, Loose.Charges.Amount AS Charge, Loose.Payments.PK AS Payment#, Loose.Payments.Amount AS Payment FROM Loose.Charges LEFT OUTER JOIN Loose.Payments ON ABS(Loose.Charges.Amount - Loose.Payments.Amount) <= @proximity GOThis produces the following result set:
Charge# Charge Payment# Payment 1 100.00 1 99.00 2 12.00 5 12.00 3 56.00 NULL NULL 4 43.00 NULL NULL 5 59.00 NULL NULL 6 998.00 6 1000.00Much more satisfactory! The reconciliation people now know that three charges have no matching payment. What if the customer mistakenly pays for something twice? Add a row to the Payments table with an amount of $1000, then re-run the last query. Here is the result set:
Charge# Charge Payment# Payment 1 100.00 1 99.00 2 12.00 5 12.00 3 56.00 NULL NULL 4 43.00 NULL NULL 5 59.00 NULL NULL 6 998.00 6 1000.00 6 998.00 7 1000.00How convenient! Weâ€™ve already solved this potential problem, as the result set shows. We can see at once that charge number 6 was paid for twice. I think that somebody in the reconciliation department owes you lunch. Summary With a little creative thinking about particular business problems, you can often devise a solution in SQL. The solution presented here is not 100% foolproof, nor does it attempt to be. For example, suppose there are two charges, $50 and $52, and one payment for either amount. The reconciliation staff have no way of knowing to which charge the payment was intended to apply, with or without your creative SQL. Nothing short of a phone call to the customer will sort this out. That problem lies outside the world of SQL. But even in this situation, the SQL presented identifies the issue and thus provides valuable assistance to the reconciliation staff. Make that two lunches.
Last updated 22 May 2009