Please see that I have corrected one of the values for the inserts. (last but one)

Insert into #t1

Values (4,5,'Debit' ,'1/5/16', 40,Null)

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

J Livingston SQL (8/16/2016)

---

can I please ask you to explain why you wish to exclude rows where the...

Hello all,

I have realised that my new data set has more data and more scenarios to be considered to come up with a solution.

During this process I learnt a lot...

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

J Livingston SQL (8/16/2016)

---

can I please ask you to explain why you wish to exclude rows where the first row is a...

J Livingston SQL (8/16/2016)

---

jssashank (8/16/2016)

---

Sure. My intention for...

Sure. My intention for adding that is to...

drew.allen (8/16/2016)

---

Code that you get on the Internet should never be considered a complete solution. It's impossible to cover every single scenario in a small sample of data. ...

drew.allen (8/12/2016)

---

Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. ...

The solution is so close. It almost feels like its one where clause that is missing.

jssashank (8/11/2016)

---

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,

...

drew.allen (8/12/2016)

---

Except for the fact that I was selecting the Transaction ID rather than the credit ID, my query produces the exact results that you say you are expecting. ...

drew.allen (8/12/2016)

---

Okay. I think I figured out where the problem is. I also realized that I didn't need one of the clauses in one of the CASE statements,...

drew.allen (8/12/2016)

---

jssashank (8/12/2016)

---

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

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

jssashank (8/11/2016)

---

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,

...