SQL2008R2 FIFO Allocation Recursive CTE

Question

Post reply

SQL2008R2 FIFO Allocation Recursive CTE

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Bruceo SSCrazy Points: 2152 More actions · Answer 1

Bruceo

SSCrazy

Points: 2152

October 30, 2015 at 12:05 am

#1836573

very handy! thanks for the help

Bruceo SSCrazy Points: 2152 More actions · Answer 2

Bruceo

SSCrazy

Points: 2152

October 30, 2015 at 12:06 am

#1836574

thanks Chris, will check this out

nghiahc89 SSC Journeyman Points: 88 More actions · Answer 3

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

Could u please help to suggest me to do this. Hope to see your reply.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

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...
Could u please help to suggest me to do this. Hope to see your reply.

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

ps.....please fix your duedates.

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

drooijackers SSC Enthusiast Points: 137 More actions · Answer 5

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

Thank you in advance