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.




Solid Quality Learning - Microsoft SQL Server 2005. Applied Techniques Step by Step
Microsoft SQL Server 2005: Applied Techniques Step by Step (Pro-Step by Step Developer)
ISBN: 0735623163
EAN: 2147483647
Year: 2006
Pages: 115

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net