# Credits and Debits

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

• 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

• 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

• @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)

• 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;`

• 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

• 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

• 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?

• 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!

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

• 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

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

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

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

• 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)