sql server - How do I calculate a running total in SQL without using a cursor?

Question

I'm leaving out all the cursor setup and the SELECT from the temp table for brevity. Basically, this code computes a running balance for all transactions per transaction.

WHILE @@fetch_status = 0
BEGIN

    set @balance = @balance+@amount

    insert into @tblArTran values ( --from artran table
                @artranid, @trandate, @type, 
                @checkNumber, @refNumber,@custid,
                @amount, @taxAmount, @balance, @postedflag, @modifieddate )


    FETCH NEXT FROM artranCursor into 
            @artranid, @trandate, @type, @checkNumber, @refNumber,
            @amount, @taxAmount,@postedFlag,@custid, @modifieddate

END

Inspired by this code from an answer to another question,

SELECT @nvcConcatenated = @nvcConcatenated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

I was wondering if SQL had the ability to sum numbers in the same way it's concatonating strings, if you get my meaning. That is, to create a "running balance" per row, without using a cursor.

Is it possible?

Answer 1

You might want to take a look at the update to local variable solution here: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl

Outperforms all other methods, but has some doubts about guaranteed row order. Seems to work fine when temp table is indexed though..

• Nested sub-query 9300 ms
• Self join 6100 ms
• Cursor 400 ms
• Update to local variable 140 ms