SQL for RunningTotals in SQL Server 2005 ...

Writing SQL queries is interesting provided you have challenging problems to solve and there is scope for interesting solutions.

As you know, SQL is set-based, meaning that you operate on a bunch of records in one-go and avoid per record or iterative computations (like the way you do using a procedural programming language like C).

I have a problem to solve - to compute running total for a set of records.
Consider the following example for a better understanding of the problem that I am trying to solve.

Here is my input data:

and here is what I expect (observe the last column where a value in one cell is equal to the running sum from row # 1 till the current row)

And here is how I solved it :)

CREATE TABLE #TmpSO(

RowNumber INT NOT NULL IDENTITY(1,1),

[Name] NVARCHAR(10) NOT NULL,

[Value] DECIMAL(18,4) NOT NULL

)

INSERT INTO #TmpSO

SELECT 'S1',10

UNION ALL

SELECT 'S2',20

UNION ALL

SELECT 'S3',30

UNION ALL
SELECT 'S4',40
UNION ALL
SELECT 'S5',50

;WITH wRunningTotals
AS
(

SELECT L.RowNumber,
SUM(R.[Value]) AS RunningTotal

FROM #TmpSO L,

#TmpSO R

WHERE L.RowNumber >= R.RowNumber

GROUP BY L.RowNumber

)

SELECT T.[Name],

T.[Value],

W.RunningTotal

FROM #TmpSO T JOIN wRunningTotals W ON T.RowNumber = W.RowNumber

SELECT [Name],[Value] FROM #TmpSO

DROP TABLE #TmpSO