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 SalesDate | 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 ExampleSub-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. |