# SQL2008R2 FIFO Allocation Recursive CTE

• very handy! thanks for the help

• thanks Chris, will check this out

• Dear Christ,

i have found your solution, it's so helpful but in my case i would like to calculate how many days that customer late for installment payment.

ex :

-- Set up sample data

declare @Deductions TABLE (InstID int IDENTITY(1,1),InstAmt money, DueDate datetime);

insert @Deductions (DeductionAmount) VALUES (1000, '20160103'),(200, '2016018'),(50, '2016013'),(600, '20160118');

declare @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money, PaymentDate datetime);

insert @Payments (PaymentAmount, PaymentDate) VALUES (500,'20160101'),(200, '20160105'),(400, '20160110'),(1000,'20160125');

at the time allocate the payment i need to split the date...

• nghiahc89 (11/29/2016)

Dear Christ,

i have found your solution, it's so helpful but in my case i would like to calculate how many days that customer late for installment payment.

ex :

-- Set up sample data

declare @Deductions TABLE (InstID int IDENTITY(1,1),InstAmt money, DueDate datetime);

insert @Deductions (DeductionAmount) VALUES (1000, '20160103'),(200, '2016018'),(50, '2016013'),(600, '20160118');

declare @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money, PaymentDate datetime);

insert @Payments (PaymentAmount, PaymentDate) VALUES (500,'20160101'),(200, '20160105'),(400, '20160110'),(1000,'20160125');

at the time allocate the payment i need to split the date...

for clarification can you please post what your expected results are, based on the sample data above.

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

• ChrisM@Work - Thursday, October 29, 2015 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 ENDFROM #Deductions dFULL OUTER JOIN #Payments pON p.[from] < d.[to] AND p.[to] > d.[from] ORDER BY ISNULL(d.DeductionID,1000000), p.PaymentID;

this may be a silly question but how do I figure out how much was paid from each payment
So row
1: 500
2: 200
3: 300
4: 100
5: 100
6: 50
7: 600