drew.allen (8/11/2016)

---

I found a faster solution (at least on this small dataset).

;

WITH totals AS (

SELECT *, ABS(Amount) AS Amt, SUM(ABS(Amount)) OVER(PARTITION BY Customer, TransactionType ORDER BY Date ROWS UNBOUNDED PRECEDING...

J Livingston SQL (8/11/2016)

---

is this close?

--code based on ChrisM fine work at http://www.sqlservercentral.com/Forums/Topic1731617-391-1.aspx

CREATE TABLE #t1

(TransactionID INT,

Customer INT,

TransactionType...

pietlinden (8/10/2016)

---

Okay... Could you post what the solution looks like given the data you originally posted? (Or did you do that already?)

So you would apply the new payment to the...

pietlinden (8/10/2016)

---

Awesome... now I know what the question is! <g>

You're doing FIFO (a queue), but with money. So the oldest debt gets paid first, and if there's any...

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

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

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

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

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

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

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