Here are solutions from that article (BTW, in SQL Server 2005 and up you can also use partition by clause)


Creating the Test Data
I used the code shown below to create the base table and a few thousand rows of test data. It is important to add enough data so that the efficiency of the three solutions can be accurately measured. In other words, with only a few rows of data they all seem efficient.

CREATE TABLE Sales (DayCount smallint, Sales money)
CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)
go
INSERT Sales VALUES (1,120)
INSERT Sales VALUES (2,60)
INSERT Sales VALUES (3,125)
INSERT Sales VALUES (4,40)

DECLARE @DayCount smallint, @Sales money
SET @DayCount = 5
SET @Sales = 10

WHILE @DayCount < 5000
 BEGIN
  INSERT Sales VALUES (@DayCount,@Sales)
  SET @DayCount = @DayCount + 1
  SET @Sales = @Sales + 15
 END

The Three Solutions

The three different solutions I tested are shown below. The execution time with and without a clustered index on DayCount is shown at the top of each batch.
Solution 1: Temp Table/Cursor

(NoIndex = 2 secs, Index = 2 secs)

CREATE TABLE #Sales (DayCount smallint, Sales money, RunningTotal money)

DECLARE @DayCount smallint,
        @Sales money,
        @RunningTotal money

SET @RunningTotal = 0

DECLARE rt_cursor CURSOR
FOR
SELECT DayCount, Sales
FROM Sales

OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales

WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @RunningTotal = @RunningTotal + @Sales
  INSERT #Sales VALUES (@DayCount,@Sales,@RunningTotal)
  FETCH NEXT FROM rt_cursor INTO @DayCount,@Sales
 END

CLOSE rt_cursor
DEALLOCATE rt_cursor

SELECT * FROM #Sales ORDER BY DayCount

DROP TABLE #Sales

Solution 2: The "Celko" Solution

(NoIndex = 25 secs, Index = 20 secs)

SELECT DayCount,
       Sales,
       Sales+COALESCE((SELECT SUM(Sales) 
                      FROM Sales b 
                      WHERE b.DayCount < a.DayCount),0)
                         AS RunningTotal
FROM Sales a
ORDER BY DayCount

Solution 3: The "Guru's Guide" Solution

(NoIndex = 38 secs, Index = 17 secs)

SELECT a.DayCount,
       a.Sales,
       SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales

>Oopss.. missed a field
>
>
>SomeId Month Value RT
>1 Jan 10 10
>1 Feb 20 30
>1 Mar 30 60
>
>I noted the link you once posted. But I don't have a login for the website.
>
>Generally you can do SUM(somevalue) over (Partition by SomeID), but that's with Oracle.
>SQL Server seems to make this more complicated, I don't think this can be done.
>
>>Hey all,
>>
>>I have a query that needs running totals by the SomeId and month. I think SQL Server complicates the matter and I might not be able to do it. The query is like this:
>>
>>Select SomeID, Month , *Running Total Here*
>>From ( Some really crazy complicated query ) CrazyQuery
>>Order by SomeId, Month
>>
>>This seems like it should be fairly simple but from what I understand, I'm going to have to create another crazyquery and reference the dates.. Am I incorrect about this?
>>
>>Thanks,
>>Dan
>
>Dan,
>
>Can you please be more specific with your question? Do you need Running total algorithm - if yes, then search here, I posted good link before.