Sorry, I didn't realize that you were on SQL 2005. You need SQL 2012 to use this construct. I should pay more attention to which forum I'm in.

Drew

ChrisM@Work (8/18/2016)

---

-- set up some data to code against

DROP TABLE #MyTable

SELECT *

INTO #MyTable

FROM (

SELECT 123, 1 UNION ALL

SELECT 456, 2 UNION ALL

SELECT 789, 1 UNION ALL

SELECT 789, 2 UNION ALL

SELECT...

This non-recursive CTE works with pre-aggregated (and mixed) data.

;

WITH running_total AS (

SELECT *, SUM(mt.copies) OVER(ORDER BY mt.subscriberid , mt.copies ROWS UNBOUNDED PRECEDING) AS rt

FROM #MyTable mt

)

SELECT rt.subscriberid,

CASE

WHEN rt.rt <= @num_records...

Are you aggregating your data in your query or is it pre-aggregated? That is, do you have two records for 456 and three records for 789, or do you...

DesNorton (8/17/2016)

---

Unless my understanding is incorrect, I believe that the movement in this solution is the wrong way around.

To prove this, let's take a look at only the first movement...

I'm writing an article explaining my solution to Credits and Debits and am looking for two or three people to review it before I submit it. Any takers?

Drew

I realized this morning that my code needed a slight update. My updates sample data had the same ordering for the from_seq and the to_seq, but that is not...

Here is a version that allows you to update multiple records at the same time.

DECLARE @A TABLE (

seqINT,

orig_seq INT

)

INSERT @A( seq, orig_seq)

VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5,...

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. That's...

John Mitchell-245523 (8/15/2016)

---

I'm going to have to disagree there, I'm afraid. Suppose the query had originally been written with an outer join, with the filter (quite correctly) in the...

jssashank (8/15/2016)

---

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

John Mitchell-245523 (8/15/2016)

---

There's no difference. But don't take my word for it - compare the execution plans and see for yourself.

Now, if the join were an outer (instead of...

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

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

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