Computing Running Totals

Sometimes you need to report cumulative values in order to display the progress of some kind of operation. For example, you might have to report on the daily progression of sales. The results would look something like this:

Table 5-6. Daily Progression of Sales

Date	Sales	Total Sales
7/1/2001	$ 665262.96	$ 665262.96
7/2/2001	15394.33	680657.29
7/3/2001	16588.46	697245.75
7/4/2001	7907.98	705153.72
7/5/2001	16588.46	721742.18
7/6/2001	15815.95	737558.13
7/7/2001	8680.48	746238.61
7/8/2001	8680.48	754919.10
7/9/2001	23105.31	778024.40
7/10/2001	11664.97	789689.37
7/11/2001	15815.95	805505.32
7/12/2001	15618.95	821124.28
7/13/2001	7907.98	829032.25
7/14/2001	27677.92	856710.17
7/15/2001	12409.84	869120.02
7/16/2001	15815.95	884935.97
...	...	...

As you can see, the Total Sales values are equal to all the previous dates' totals plus the current row's total. This type of total is called a running total because the total is calculated up to and including the current record.

The query for running totals has two parts. The first part is simple. Just sum the sales grouped by the date:

SELECT OrderDate, SUM(TotalDue) AS Sales FROM Sales.SalesOrderHeader AS A GROUP BY OrderDate ORDER BY OrderDate

We want to find the running total for one specific date using something like the following theoretical query:

--This is not valid SQL SELECT SUM(TotalDue) AS Expr1 FROM Sales.SalesOrderHeader WHERE (OrderDate <= <Specific_Date>)

Note that the above is not a valid T-SQL script; it is an explanation of how we might calculate a running total for a particular point in time. <Specific_Date> is not valid for T-SQL, but represents one specific date for our theoretical query. To turn this theory into useful T-SQL, we can use the OrderDate field as the specific date for each row. (The scripts in this section are included in the samples as RunningTotalsExamplesFromText.sql in the \SqlScripts folder.)

Calculating a Running Total Using a SubQuery

1.	Use a sub-query to get all the values in the same result set. Save this query as SubQueryMethod.sql. SELECT OrderDate, SUM(TotalDue) AS Sales, ( SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE (OrderDate <= A.OrderDate) ) AS [Actual Sales] FROM Sales.SalesOrderHeader AS A GROUP BY OrderDate ORDER BY OrderDate
2.	Look at the Estimated Execution Plan in SQL Server Management Studio. You will see two queries running in parallel to get the result sets.

Let us try something different. We can encapsulate the running total in a user-defined function.

Calculating a Running Total Using a User-Defined Function

1.	First, create the user-defined function by executing the following script: CREATE FUNCTION SalesToDate ( @ThisDate datetime ) RETURNS money AS BEGIN RETURN (SELECT SUM(TotalDue) AS Expr1 FROM Sales.SalesOrderHeader WHERE (OrderDate <= @ThisDate)) END
2.	Now execute the following query. Save the queries in step 1 and this step as UserDefFunctionMethod.sql. SELECT OrderDate, SUM(TotalDue) AS Sales, dbo.SalesToDate(A.OrderDate) AS [Actual sales] FROM Sales.SalesOrderHeader AS A GROUP BY OrderDate ORDER BY OrderDate

Which technique is better? To compare the two, use the following procedure.

Comparing the Performance of Two Queries

1.	From SQL Server Management Studio, open the Query menu.
2.	Select the Include Client Statistics menu item.
3.	Execute the two sets of queries that you saved above a second time. You will now see the Client Statistics tab available along with the results. Use the information provided to learn about the costs of the two different kinds of queries.

The following table shows a subset of the statistics for the two queries.

Note

The table shows only the part of the available statistics.

Table 5-7. Client Statistics Example

Sub-Query Method	Trial 1	Average
Client processing time	10375	10375.000
Total execution time	10835	10835.000
Wait time on server replies	460	460.000
User-Defined Function Method	Trial 1	Average
Client processing time	35677	35677.000
Total execution time	39502	39502.000
Wait time on server replies	3825	3825.000

As you can see, the running time for the query using the user-defined function is more than three times greater than the running time for the query using a sub-query.