October 28, 2015 at 10:02 am
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
October 28, 2015 at 11:45 am
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.
October 29, 2015 at 12:41 am
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
October 29, 2015 at 7:01 am
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;
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 29, 2015 at 7:25 am
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
October 29, 2015 at 7:38 am
You're welcome, thanks for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 29, 2015 at 8:08 am
ChrisM@Work (10/29/2015)
Try this - the result of some fiddling around earlier:
-- Set up sample dataDECLARE @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
October 29, 2015 at 8:14 am
J Livingston SQL (10/29/2015)
ChrisM@Work (10/29/2015)
Try this - the result of some fiddling around earlier:
-- Set up sample dataDECLARE @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.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 29, 2015 at 8:20 am
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
October 29, 2015 at 8:54 am
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
October 29, 2015 at 8:56 am
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?
October 29, 2015 at 9:31 am
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
October 29, 2015 at 10:26 am
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 29, 2015 at 10:38 am
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
October 29, 2015 at 11:20 am
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;
For fast, accurate and documented assistance in answering your questions, please read this article.
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply