Applying payment across multiple items

[BFarley]

I have a process that I'm converting to T-SQL from VBA (which of course uses looping), and I wanted to see if it could be used without touching each row.

Here's the situation. A payment is received. There are one or more line items against which the payment can be posted. (You may have seen similar behavior in QuickBooks or MS Pro Accounting)

Example: $500 received

Item 1 = $250
Item 2 = $200
Item 3 = $50
Item 4 = $100

What needs to happen is that the $500 is distributed against these items.

Item 1 = $250 of $500 distributed; $250 remaining
Item 2 = $200 of remaining $250 distributed; $50 remaining
Item 3 = $50 of remaining $50 distributed; $0 remaining
Item 4 = nothing happens since $0 remaining

I can easily figure out how to do this row-by-row... but wanted to see if there's a set-based solution.

Thanks in advance for your guidance!


Bryant Farley
EG&G Technical Services

 

[SQLSister]

How do you determine how much to distribute against each item?


"NOTHING is more important in a database than integrity." ESquared

 

[BFarley]

If an item is less than the total remaining balance, then the routine would apply as much of the balance as possible, to either (1) "pay off" the item or (2) deplete the remaining balance completely.

I guess a real-world better example would be receiving a payment on multiple invoices ... allocating a portion of the $$ received to each of several invoices, starting at the top and going downward.

Does that make sense?


Bryant Farley
EG&G Technical Services

 

[RiverGuy]

I can't think of a straightforward, purely set based solution. Here's an example where you can use a table variable/temp table to loop through the rows and figure out the amount to apply (without the $250 limit you have in your original post). You could use the output to join to your actual table to perform the update.

DECLARE @AmountToApply MONEY
DECLARE @AmountToApplyTemp MONEY
DECLARE @InvoiceID INT
DECLARE @AmountDueTemp MONEY
SELECT @AmountToApply = 170
SELECT @AmountToApplyTemp = @AmountToApply


DECLARE @Invoices TABLE (
InvoiceID INT IDENTITY,
AmountDue MONEY,
AmountToApply MONEY
)

INSERT INTO @Invoices (AmountDue) SELECT 100
INSERT INTO @Invoices (AmountDue) SELECT 50
INSERT INTO @Invoices (AmountDue) SELECT 60
INSERT INTO @Invoices (AmountDue) SELECT 80

WHILE (SELECT COUNT(*) FROM @Invoices WHERE AmountToApply IS NULL) > 0
BEGIN
	SELECT @InvoiceID = InvoiceID FROM (SELECT TOP 1 * FROM @Invoices WHERE AmountToApply IS NULL ORDER BY InvoiceID) a
	SELECT @AmountDueTemp = AmountDue FROM @Invoices WHERE InvoiceID = @InvoiceID
	IF @AmountDueTemp >= @AmountToApplyTemp
	BEGIN
		UPDATE @Invoices SET AmountToApply = @AmountToApplyTemp WHERE InvoiceID = @InvoiceID
		UPDATE @Invoices SET AmountToApply = 0 WHERE AmountToApply IS NULL
	END
	ELSE
	BEGIN
		UPDATE @Invoices SET AmountToApply = AmountDue WHERE InvoiceID = @InvoiceID
		SELECT @AmountToApplyTemp = @AmountToApplyTemp - @AmountDueTemp
	END
END

SELECT * FROM @Invoices

 

[BFarley]

I had something similar to that, RiverGuy ... trying to think in a more set-based manner than strictly along my typical looping programming ways


Bryant Farley
EG&G Technical Services