# SQL2008R2 FIFO Allocation Recursive CTE

• Hi All,

I have been presented with a task to write a FIFO allocation Recursive CTE (or any other solution that ideally does not use a cursor!)

The scenario is: We have two tables, Deductions and Payments and I need to use a FIFO script to Allocate Payments to Deductions, making sure that the Deductions are fully allocated using one or more Payments without exceeding the payment amount for each payment.

Essentially this is a many to many relationship.

To try and solve this I have created the following table variables

`DECLARE @Deductions TABLE`

`(`

`DeductionID int IDENTITY(1,1),`

`DeductionAmount money`

`)`

`INSERT @Deductions`

`(DeductionAmount)`

`VALUES`

`(1000),(200),(50),(600)`

`DECLARE @Payments TABLE`

`(`

`PaymentID int IDENTITY(1,1),`

`PaymentAmount money`

`)`

`INSERT @Payments`

`(PaymentAmount)`

`VALUES`

`(500),(200),(400),(1000)`

Now what should happen is the following:

Deduction 1, should have the full Payment Amount for Payments 1 and 2 and 300 of the 3rd Payment.

Deduction 2, should start off with the balance of 100 for the 3rd payment and settle the rest from the 4th payment and so on.

the allocations should look like this

DeductionID DeductionAmount PaymentID PaymentAmount DeductionBalance PaymentBalance

1 1000.00 1 500.00 500.00 0.00

1 1000.00 2 200.00 300.00 0.00

1 1000.00 3 400.00 0.00 100.00

2 200.00 3 400.00 100.00 0.00

3 50.0 4 1000.00 0.00 950.00

4 600.00 4 1000.00 0.00 350.00

Any ideas on how I can achieve this?

Below is what i have come up with so far, it does not work properly at all due to the recursive join and link to the PaymentID -1, i just was not sure how to put the two result sets together for the allocations and hande my balances and running sums

Any help would be appreciated!

Thanks

`DECLARE @Deductions TABLE`

`(`

`DeductionID int IDENTITY(1,1),`

`DeductionAmount money`

`)`

`INSERT @Deductions`

`(DeductionAmount)`

`VALUES`

`(1000),(200),(50),(600)`

`DECLARE @Payments TABLE`

`(`

`PaymentID int IDENTITY(1,1),`

`PaymentAmount money`

`)`

`INSERT @Payments`

`(PaymentAmount)`

`VALUES`

`(500),(200),(400),(1000)`

`;WITH Alloc AS`

`(`

`SELECT`

`D.DeductionID,`

`D.DeductionAmount,`

`CAST(0 AS int) PaymentID,`

`CAST(0 AS money) PaymentAmount,`

`DeductionAmount DeductionBalance,`

`CAST(0 AS money) PaymentBalance,`

`ROW_NUMBER() OVER (ORDER BY D.DeductionID) RowN`

`FROM @Deductions D`

`UNION ALL`

`SELECT`

`A.DeductionID,`

`A.DeductionAmount,`

`P.PaymentID,`

`P.PaymentAmount,`

`DeductionBalance - CASE WHEN DeductionBalance - P.PaymentAmount < 0 THEN DeductionBalance ELSE P.PaymentAmount END DeductionBalance,`

`CASE WHEN DeductionBalance - P.PaymentAmount > 0 THEN 0 ELSE P.PaymentAmount - DeductionBalance END PaymentBalance,`

`RowN +1`

`FROM Alloc A`

`INNER JOIN @Payments P ON A.PaymentID = P.PaymentID - 1`

`WHERE A.DeductionBalance > 0`

`)`

`SELECT`

`DeductionID, DeductionAmount, PaymentID, PaymentAmount,DeductionBalance,PaymentBalance, RowN`

`FROM Alloc`

`WHERE PaymentID <>0`

`ORDER BY DeductionID, PaymentID`

• Just so you know, a well written cursor or while loop can be better than a recursive CTE. I made a test some time ago and it's published in here: http://www.sqlservercentral.com/articles/set-based+loop/127670/

I have to go to a meeting, so I don't have much time to try this, but wanted to give you that advice.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2
• Hi Luis,

Thanks for the advice, I had not thought about the set based loop/cursor approach.

Interesting article, I will try and relook at the original problem and see if I can apply some of those principles.

Regards

Bruce

• Try this - the result of some fiddling around earlier:

`-- Set up sample data`

`DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);`

`INSERT @Deductions (DeductionAmount) VALUES (1000),(200),(50),(600);`

`DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);`

`INSERT @Payments (PaymentAmount) VALUES (500),(200),(400),(1000);`

`-- Preprocessing`

`IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`INTO #Deductions`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);`

`IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`INTO #Payments`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);`

`-- Generate result set`

`-- Note that Deduction 2 is covered by Payments 3 AND 4.`

`-- Please check your figures in your expected result set`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM #Deductions d`

`CROSS JOIN #Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

`-- You could also write it using CTE's like this:`

`WITH`

`Deductions AS (`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d),`

`Payments as (`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d)`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM Deductions d`

`CROSS JOIN Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Thanks Chris, this is exactly what I was looking for!

The db is in SQL08R2, so LAG is not going to work, but I am sure that I can get around that.

Thanks for your help

• You're welcome, thanks for the feedback.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work (10/29/2015)

Try this - the result of some fiddling around earlier:

`-- Set up sample data`

`DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);`

`INSERT @Deductions (DeductionAmount) VALUES (1000),(200),(50),(600);`

`DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);`

`INSERT @Payments (PaymentAmount) VALUES (500),(200),(400),(1000);`

`-- Preprocessing`

`IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`INTO #Deductions`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);`

`IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`INTO #Payments`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);`

`-- Generate result set`

`-- Note that Deduction 2 is covered by Payments 3 AND 4.`

`-- Please check your figures in your expected result set`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM #Deductions d`

`CROSS JOIN #Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

`-- You could also write it using CTE's like this:`

`WITH`

`Deductions AS (`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d),`

`Payments as (`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d)`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM Deductions d`

`CROSS JOIN Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

not sure if this is relevant to the case, but if there are more payments made than required to fulfil a deduction, then the results dont seem to be as I would have expected

for example

`DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);`

`INSERT @Deductions (DeductionAmount) VALUES (1000); -- note only one deduction`

`DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);`

`INSERT @Payments (PaymentAmount) VALUES (500),(200),(400),(1000);`

running the code with the above gives this output...the fourth payment is ignored....is this as expected

`+-----------------------------------------------------------------------------------------------+`

`¦ DeductionID ¦ DeductionAmount ¦ PaymentID ¦ PaymentAmount ¦ DeductionBalance ¦ PaymentBalance ¦`

`¦-------------+-----------------+-----------+---------------+------------------+----------------¦`

`¦ 1 ¦ 1000.00 ¦ 1 ¦ 500.00 ¦ 500.00 ¦ 0.00 ¦`

`¦ 1 ¦ 1000.00 ¦ 2 ¦ 200.00 ¦ 300.00 ¦ 0.00 ¦`

`¦ 1 ¦ 1000.00 ¦ 3 ¦ 400.00 ¦ 0.00 ¦ 100.00 ¦`

`+-----------------------------------------------------------------------------------------------+`

I may be wrong, but I have a feeling that this has been oversimplified by OP....I would have expected other columns such as individual accounts whereby there are many accounts with many deds/pays etc.

also...why no dates on sample data....normally these types of transactions have posted dates as well.

just my twopenneth

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• J Livingston SQL (10/29/2015)

ChrisM@Work (10/29/2015)

Try this - the result of some fiddling around earlier:

`-- Set up sample data`

`DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);`

`INSERT @Deductions (DeductionAmount) VALUES (1000),(200),(50),(600);`

`DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);`

`INSERT @Payments (PaymentAmount) VALUES (500),(200),(400),(1000);`

`-- Preprocessing`

`IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`INTO #Deductions`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);`

`IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`INTO #Payments`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;`

`CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);`

`-- Generate result set`

`-- Note that Deduction 2 is covered by Payments 3 AND 4.`

`-- Please check your figures in your expected result set`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM #Deductions d`

`CROSS JOIN #Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

`-- You could also write it using CTE's like this:`

`WITH`

`Deductions AS (`

`SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]`

`FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d),`

`Payments as (`

`SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]`

`FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d)`

`SELECT DeductionID, DeductionAmount, PaymentID, PaymentAmount,`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] ELSE 0 END`

`FROM Deductions d`

`CROSS JOIN Payments p`

`WHERE p.[from] <= d.[to] AND p.[to] >= d.[from]`

`ORDER BY d.DeductionID, p.PaymentID;`

not sure if this is relevant to the case, but if there are more payments made than required to fulfil a deduction, then the results dont seem to be as I would have expected

for example

`DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);`

`INSERT @Deductions (DeductionAmount) VALUES (1000); -- note only one deduction`

`DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);`

`INSERT @Payments (PaymentAmount) VALUES (500),(200),(400),(1000);`

running the code with the above gives this output...the fourth payment is ignored....is this as expected

`+-----------------------------------------------------------------------------------------------+`

`¦ DeductionID ¦ DeductionAmount ¦ PaymentID ¦ PaymentAmount ¦ DeductionBalance ¦ PaymentBalance ¦`

`¦-------------+-----------------+-----------+---------------+------------------+----------------¦`

`¦ 1 ¦ 1000.00 ¦ 1 ¦ 500.00 ¦ 500.00 ¦ 0.00 ¦`

`¦ 1 ¦ 1000.00 ¦ 2 ¦ 200.00 ¦ 300.00 ¦ 0.00 ¦`

`¦ 1 ¦ 1000.00 ¦ 3 ¦ 400.00 ¦ 0.00 ¦ 100.00 ¦`

`+-----------------------------------------------------------------------------------------------+`

I may be wrong, but I have a feeling that this has been oversimplified by OP....I would have expected other columns such as individual accounts whereby there are many accounts with many deds/pays etc.

also...why no dates on sample data....normally these types of transactions have posted dates as well.

just my twopenneth

Good spot, G. I've only tested the proposed solution on the sample data set provided by the OP. I think it would be trivial to tweak the query to account for your new data set though.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Good spot, G. I've only tested the proposed solution on the sample data set provided by the OP. I think it would be trivial to tweak the query to account for your new data set though.

am sure it can be tweaked Chris.....my point was really to the OP about fully defining requirements.

too often these type of queries become "dripfeed", with more and more additional requests.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• Hello,

I appreciate the thoughts and observations.

This is a theoretical allocations table, so it would only have the Payments required to fulfil the Deduction.

Usually, we would have a Receipt and an Invoice scenario whereby a user selects a Reciept (with a balance) and is provided with a list of unallocted Invoices that the Receipt could be allocated to. The allocation table would just serve as a means to show settlements with the allocated amount, date and transactions that are settled in the Allocation.

This scenario is a somewhat automated version of this.

The results are as expected, in that only the Payments needed to settle the Deduction are Allocated.

Not sure what you mean by I have oversimplified the requirements, this was just a basic shell to help me solve a business problem. I have not included all the columns in the tables, just the ones that were pertinant to this case.

You are correct in that there could be more Payments than Deductions and vice versa.

In the actual database tables and solution, there will be a lot more detail to the transactional tables, I just wanted the simplest design possible to post my question and give me some base ideas on how to solve this.

Hope this clears up my original post further.

Thanks for the valuable feedback and for taking the time to have a look at my question

• By the way, how do you format your results like that? I looked through all the shortcuts and could not figure it out!

Is that an image or just SQL tags?

• Bruceo (10/29/2015)

By the way, how do you format your results like that? I looked through all the shortcuts and could not figure it out!

Is that an image or just SQL tags?

http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

copy results from SSMS...paste into above link....format as Unicode....paste into SSC with plain code tags

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• J Livingston SQL (10/29/2015)

Bruceo (10/29/2015)

By the way, how do you format your results like that? I looked through all the shortcuts and could not figure it out!

Is that an image or just SQL tags?

http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

copy results from SSMS...paste into above link....format as Unicode....paste into SSC with plain code tags

Thanks G, that's nice.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work (10/29/2015)

J Livingston SQL (10/29/2015)

Bruceo (10/29/2015)

By the way, how do you format your results like that? I looked through all the shortcuts and could not figure it out!

Is that an image or just SQL tags?

http://www.sensefulsolutions.com/2010/10/format-text-as-table.html

copy results from SSMS...paste into above link....format as Unicode....paste into SSC with plain code tags

Thanks G, that's nice.

all thanks to this excellent post

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• A change to the earlier query after mixing up some more data:

`SELECT`

`DeductionID, DeductionAmount, --d.[from], d.[to],`

`PaymentID, PaymentAmount, --p.[from], p.[to],`

`DeductionBalance = CASE WHEN d.[to] > p.[to] THEN d.[to] - p.[to] ELSE 0 END,`

`PaymentBalance = CASE WHEN p.[to] > d.[to] THEN p.[to] - d.[to] WHEN d.[to] IS NULL THEN PaymentAmount ELSE 0 END`

`FROM #Deductions d`

`FULL OUTER JOIN #Payments p`

`ON p.[from] < d.[to] AND p.[to] > d.[from]`

`ORDER BY ISNULL(d.DeductionID,1000000), p.PaymentID;`

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw