Loose associations

from the Artful SQL Server & Access Tips List


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]
GO

Now 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)
GO

The 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 BETWEEN clause, but I have a better idea: let’s use the Abs() function instead. That makes the expression of the range of interesting values much cleaner.

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
GO

Before 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.00

The 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
GO

This 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.00

Much 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.00


How 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.

Return to the Artful SQL Server & Access Tips page