August 9, 2016 at 6:59 pm
Hello,
This is my first post here , so please be patient.
I am trying to build a logic in SQL server 2012 where the Debit amount should always be assigned to the Previous credit amount which still has an outstanding balance.
(unable to format this table any better)
TransactionIDCustomerTransactionTypeDate AmountCreditID
1 1Credit 1/1/16-20 1
2 1Debit 1/2/1620
3 1Credit 1/3/16-100 2
4 1Debit 1/4/1650
5 1Debit 1/5/1650
6 1Credit 1/6/16-50 3
7 1Debit 1/7/1620
8 1Credit 1/8/16-50 4
9 1Debit 1/7/1620
So in the example above Debit 20$ ( Transaction ID 2) should be assigned to CreditID1
and TransactionId 4 and 5 should be assigned to CreditID 2
TransactionID 7 and 9 should be assigned to CreditID 3
and Credit 4 should show remaining balance as -50 $
I used the gap and Island approach here , but the problem is using that I was able to assign the debit to the last credit ID .
It messes my logic when there a Credit happens before the earlier credit is net to 0.( ex Credit rank 3 and Credit rank 4)
Please let me know if my question is not clear. Happy to explain. Part of me thinks I should be using recursive CTE's , but not 100% sure how it can help me here.
August 9, 2016 at 7:59 pm
just wondering... what if you used a windowing function and did a running total of the value (credits are positive, debits are negative). Then it would act like a checkbook
SUM(value) OVER (PARTITION BY AccountNumber
ORDER BY TransactionDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningTotal
August 9, 2016 at 8:00 pm
Any chance you could post your data as a CREATE TABLE followed by a series of INSERT statements so I can setup a sandbox on my side?
Your expected results per your test data would also be nice to see written out so I can test any potential solutions before posting back.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 9, 2016 at 8:13 pm
@pietlinden I tried that. But the problem occurs when in a situation where the there is a partial debit between two credits (last example
TransactionID 7 and 9 should be assigned to CreditID 3)
August 9, 2016 at 8:17 pm
Here's my setup so other folks can play along:
--TransactionIDCustomerTransactionTypeDateAmount
SELECT CustID
, TransID
, TType
, TDate
, Amt
, SUM(Amt) OVER (PARTITION BY CustID
ORDER BY TransID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningAmt
FROM (
SELECT 1 AS TransID,1 AS CustID,'Credit' AS TType, '1/1/16' AS TDate,-20 As Amt
UNION ALL
SELECT 2,1,'Debit','1/2/16',20
UNION ALL
SELECT 3,1,'Credit','1/3/16',-100
UNION ALL
SELECT 4,1,'Debit','1/4/16',50
UNION ALL
SELECT 5,1,'Debit','1/5/16',50
UNION ALL
SELECT 6,1,'Credit','1/6/16',-50
UNION ALL
SELECT 7,1,'Debit','1/7/16',20
UNION ALL
SELECT 8,1,'Credit',' 1/8/16',-50
UNION ALL
SELECT 9,1,'Debit','1/7/16',20
) x;
August 9, 2016 at 8:23 pm
Orlando Colamatteo (8/9/2016)
Any chance you could post your data as a CREATE TABLE followed by a series of INSERT statements so I can setup a sandbox on my side?Your expected results per your test data would also be nice to see written out so I can test any potential solutions before posting back.
Create table #t1
(
TransactionID int
,Customer int
,TransactionType varchar(10)
,[Date] date
,Amount money
,CreditID int)
Insert into #t1
Values (1,1,'Credit', '1/1/16',-20 ,1 )
Insert into #t1
Values (2,1,'Debit' , '1/2/16',20 ,Null )
Insert into #t1
Values (3,1,'Credit','1/3/16',-100,2 )
Insert into #t1
Values (4,1,'Debit' ,'1/4/16',50 ,Null )
Insert into #t1
Values (5,1,'Debit' ,'1/5/16',50 ,Null )
Insert into #t1
Values (6,1,'Credit','1/6/16',-50 ,3 )
Insert into #t1
Values (7,1,'Debit' ,'1/7/16',20 ,Null )
Insert into #t1
Values (8,1,'Credit','1/8/16',-50 ,4 )
Insert into #t1
Values (9,1,'Debit' ,'1/9/16',20 , Null )
THE Final result would be something like
CreditID remaining Balance LastRedeemed date
1 0.00 '1/2/16'
2 0.00 '1/5/16'
3 10.00 '1/9/16'
4 -50.00 Null
August 9, 2016 at 8:53 pm
Could you please explain the logic of how you get to the expected result? The running total over the CustomerID is really close, but wrong somehow... but why?
How is the logic you are trying to implement different than a pure running total? If there are two transactions that should be grouped together, how are they to be identified?
The result I get when I do a running total as shown is I get a zero balance at Transactions #2 and #5. Therefore my logic must be wrong. how?
Pieter
August 9, 2016 at 9:07 pm
pietlinden (8/9/2016)
Could you please explain the logic of how you get to the expected result? The running total over the CustomerID is really close, but wrong somehow... but why?How is the logic you are trying to implement different than a pure running total? If there are two transactions that should be grouped together, how are they to be identified?
The result I get when I do a running total as shown is I get a zero balance at Transactions #2 and #5. Therefore my logic must be wrong. how?
Pieter
Sure, I ran into the same issue. The debit should be taken from the first "non zeroed" Credit. So the
Debit in transaction 2 will be debited from credit1 and credit1 will become 0
Transaction 4 and 5 will be taken out of credit2 (-100$) and will be 0ed.
Transaction 7 will be removed from credit3 and there will be -30$ in balance
Transaction 9 will be again removed from the first non 0ed Credit which is credit3 and the remaining balance will be -10$
Credit4 is still untouched.
makes sense?
August 9, 2016 at 9:55 pm
I give. I don't get it. I see how in theory you should apply debits to credits or whatever, but I'm not sure why it matters. I must be missing something (maybe I'm tired). How is $10 on date 1 different from $10 a day later? I'm not trying to be a twit. I just don't understand. Is there something going on behind the scenes that explains this? Like you're calculating total interest on something? Even so, I don't see how this makes sense.
So I guess I give. See if Jeff Moden can sort it. I guess I gotta turn in my light sabre!
August 9, 2016 at 10:07 pm
pietlinden (8/9/2016)
I give. I don't get it. I see how in theory you should apply debits to credits or whatever, but I'm not sure why it matters. I must be missing something (maybe I'm tired). How is $10 on date 1 different from $10 a day later? I'm not trying to be a twit. I just don't understand. Is there something going on behind the scenes that explains this? Like you're calculating total interest on something? Even so, I don't see how this makes sense.So I guess I give. See if Jeff Moden can sort it. I guess I gotta turn in my light sabre!
Ha Ha... The reason why we need information at this particular detail is we will use this to calculate the average time a person holds on to a credit and figure out the credit and redemption patterns.
That is the main reason why we want to treat every credit as a different entity and remove the debit from the first remaining credit.
August 9, 2016 at 10:09 pm
jssashank (8/9/2016)
pietlinden (8/9/2016)
Could you please explain the logic of how you get to the expected result? The running total over the CustomerID is really close, but wrong somehow... but why?How is the logic you are trying to implement different than a pure running total? If there are two transactions that should be grouped together, how are they to be identified?
The result I get when I do a running total as shown is I get a zero balance at Transactions #2 and #5. Therefore my logic must be wrong. how?
Pieter
Sure, I ran into the same issue. The debit should be taken from the first "non zeroed" Credit. So the
Debit in transaction 2 will be debited from credit1 and credit1 will become 0
Transaction 4 and 5 will be taken out of credit2 (-100$) and will be 0ed.
Transaction 7 will be removed from credit3 and there will be -30$ in balance
Transaction 9 will be again removed from the first non 0ed Credit which is credit3 and the remaining balance will be -10$
Credit4 is still untouched.
makes sense?
Do you mind sharing the logic you have so far. It might help to ring a bell
August 9, 2016 at 11:18 pm
Oh wait... I think I get it. You're kind of looking for patterns in when each debt gets paid off... so older debts (which are theoretically accruing interest) are more important to pay off first, Hence the First In First Out application of payments.
I seem to remember Jeff Moden writing an article about this kind of thing Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]
Maybe that will help.
August 9, 2016 at 11:31 pm
pietlinden (8/9/2016)
How do you mean? Do you mean "Explain what the code is doing"?It's doing a running total. It's a typical use of windowing functions... took it straight out of Itzik Ben-Gan's book.
The "window" is just the grouping level (but in windowing functions you don't lose the details the way you do with aggregate functions like SUM).
The OVER is analogous to GROUP BY in a normal aggregate... the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW says "grab all the records in this partition/group and do something with them (in this case SUM)...
Is that what you mean? If not, please clarify.
Oh no. I thought you had a different piece of code. I understood the code you shared earlier 🙂 I tried something similar. The piece I am missing is how to increase deduct from the earlier credit which is not completely used. Thanks for being involved . I appreciate it.
August 9, 2016 at 11:41 pm
I think I get it... you want to apply amounts to the oldest debt first, and then carry forward any remaining money and apply it to the next oldest debt. Right? If so, then I'm really confused, because that's what the Running Total essentially does. The ORDER BY clause in the window orders the debits and credits in chronological order, so the money each transaction is applied to the current balance. That's just the way the windowing function works. (If you want an explanation of all the guts of that kind of thing, read Itzik Ben-Gan's book.)
You could calculate how long each balance lasted... you'd just do the windowing stuff as shown and then use LAG to get the previous transaction or include PARTITION to get the last one of a specific type (I think... haven't tried it.) Makes me understand why credit card companies just calculate average balance over a monthly period... doing super nitty-gritty stuff like this is computationally intense.
August 9, 2016 at 11:51 pm
pietlinden (8/9/2016)
I think I get it... you want to apply amounts to the oldest debt first, and then carry forward any remaining money and apply it to the next oldest debt. Right? If so, then I'm really confused, because that's what the Running Total essentially does. The ORDER BY clause in the window orders the debits and credits in chronological order, so the money each transaction is applied to the current balance. That's just the way the windowing function works. (If you want an explanation of all the guts of that kind of thing, read Itzik Ben-Gan's book.)You could calculate how long each balance lasted... you'd just do the windowing stuff as shown and then use LAG to get the previous transaction or include PARTITION to get the last one of a specific type (I think... haven't tried it.) Makes me understand why credit card companies just calculate average balance over a monthly period... doing super nitty-gritty stuff like this is computationally intense.
You are absolutely right. That's the logic am looking for. Let me try this code tomorrowill and I ll update you here . Hopefully this works.
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply