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

SSRS 2005 - Difference between ROUND(x,2) and N2

Recently I have an interesting finding while using ROUND function in SSRS and in SQL Server 2005.

Let me take an example and walk you through my experience:

Consider the following three values:

Input :
1) 10.422
2) 10.425
3) 10.427

Applying ROUND(x,2) function on the above values in an SQL query would give you the following output (respectively)

1) 10.42
2) 10.43
3) 10.43

As expected, the ROUND(x,2) function does a floor when the last digit is less than 5 and does a ceil when the last digit is equal to or greater than 5

So far, so good, now if you do the same thing in SSRS 2005, the results are suprising:

1) 10.42
2) 10.42
3) 10.43

Observe that the ROUND(x,2) function in SSRS 2005 did a floor even when the last digit is equal to 5 !!

I know that it is a bit frustrating when your client calls up and brings this issue up in a system that is in PRODUCTION !!

Here is a solution to make the ROUND(x,2) function in SSRS 2005 give you the expected result in such a situation:

Use ROUND(x,2,System.MidpointRounding.AwayFromZero)

Isn't life so cool now :)

Let me introduce some confusion and solve it myself ;-)

In SSRS 2005, you can set the format on a number field to N2. If you apply N2 and run the report, you would see that it presents the value as expected (just like how a ROUND(x,2) function does in SQL 2005).


Is N2 equal to ROUND(x,2,,System.MidpointRounding.AwayFromZero) ?

The answer is NO - when you look at the report, there is no visible difference but when you export the report to excel, you would observe that the value (after applying N2) is still output as 10.425 although the formatting is set to two decimals.

Here are the screenshots illustrating the difference: