Calculate a Running Total in SQL Server

Question

Imagine the following table (called TestTable):

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

I would like a query that returns a running total in date order, like:

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008.

I am particularly interested in this sort of method that uses the aggregating-set-statement trick:

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
   SELECT id, somedate, somevalue, null
   FROM TestTable
   ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

Although I have heard there are problems with this because you can't always be sure that the UPDATE statement will process the data in the right sequence, it is still incredibly efficient. We might be able to find some conclusive solutions to that problem.

But perhaps others have ideas for different approaches. Can someone please help me with this?

Answer 1

The problem is that the SQL Server implementation of the Over clause is somewhat limited.

Oracle (and ANSI-SQL) allow you to do things like:

 SELECT somedate, somevalue,
  SUM(somevalue) OVER(ORDER BY somedate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Table

There isn't a clear remedy to this issue provided by SQL Server. Though I will need to conduct some benchmarking on significant results, my intuition tells me that this is one of the rare situations in which a cursor is the fastest.

The update method is useful, but I think it's a bit flimsy. It appears that the order of the primary key will be followed if you are changing an entire table. Therefore, you will probably be secure if you set your date as a primary key ascending. However, you are depending on a SQL Server implementation detail that is not documented (especially if the query is ultimately handled by two procs). 

I hope this helps you.