Digging Out the Data

Digging out the Data

Congratulations to Rafael Mauricio Cotes Londoño, business intelligence products director for Alcuadrado S.A. in Medellín, Colombia, and Peter Borchers, database developer at Herbert Smith in London, England. Rafael won first prize of $100 for the best solution to the June Reader Challenge, "Digging out the Data." Peter won second prize of $50. Here’s a recap of the problem and the solution to the June Reader Challenge.

Problem

Mark programs a database for an Internet retailer. The database is currently hosted on a server running SQL Server 7.0, which the company will soon upgrade to SQL Server 2000. As part of his primary duties, Mark generates reports for the sales people. One of his tasks requires him to assemble the following data for the company’s 20 top-selling products, ordering the data first by totals for each product sold, then by the summary rows.

• Total sales for each product sold by category
• Quantity ordered for each product by category
• Total sales and quantity ordered for all products by category
• Total sales and quantity ordered across all products and categories

Because the Products, Categories, and Order Details tables of the Northwind sample database closely approximate his database schema, Mark decides to use them to try various approaches to retrieving the data. He also wants to avoid using temporary tables and cursors for this assignment. Help Mark write a query that generates the data he wants, yet lets him easily add other filters and parameters as needed.

Solution

To retrieve the list of 20 top-selling products by category, Mark can use the TOP clause of the SELECT statement, a feature that SQL Server 7.0 introduced. The following SELECT statement returns the data based on the Products, Categories, and Order Details tables of the Northwind database:

SELECT TOP 20 c.\[CategoryName\] , p.\[ProductName\] ,
		SUM( od.\[Quantity\] ),
		SUM( od.\[Quantity\] * od.\[UnitPrice\] * 
		(1 - od.\[Discount\]) ) AS SoldAmount
FROM \[Order Details\] AS od
JOIN \[Products\] AS p
	ON p.\[ProductID\] = od.\[ProductID\]
	JOIN \[Categories\] AS c
	ON c.\[CategoryID\] = p.\[CategoryID\]
GROUP BY c.\[CategoryName\], p.\[ProductName\]
ORDER BY SoldAmount DESC

Next, Mark reads up on the CUBE and ROLLUP operators, which facilitate analytic processing. When he realizes that the ROLLUP operator will help him generate the summary totals for the top 20 products, Mark incorporates it into the following derived table:

SELECT t20.\[CategoryName\] , t20.\[ProductName\] ,
		SUM( t20.\[Orders\] ) AS TotalOrders,
		SUM( t20.\[SoldAmount\] ) AS TotalSoldAmount
FROM (
	SELECT TOP 20 c.\[CategoryName\] , p.\[ProductName\] ,
			SUM( od.\[Quantity\] ) ,
			SUM( od.\[Quantity\] * od.\[UnitPrice\] *
			(1 - od.\[Discount\]) ) AS SoldAmount
		FROM \[Order Details\] AS od
		JOIN \[Products\] AS p
		  ON p.\[ProductID\] = od.\[ProductID\]
		JOIN \[Categories\] AS c
               ON c.\[CategoryID\] = p.\[CategoryID\]
	GROUP BY c.\[CategoryName\], p.\[ProductName\]
	ORDER BY SoldAmount DESC
) AS t20 (CategoryName, ProductName , Orders, SoldAmount )
GROUP BY t20.\[CategoryName\], t20.\[ProductName\]
WITH ROLLUP

In this way, the ROLLUP operator generates aggregates for a hierarchy of products and categories. To order the results with the required summary totals at the bottom, Mark can now use the GROUPING function in the outermost ORDER BY clause, as the following snippet of code shows:

ORDER BY GROUPING( t20.\[CategoryName\] ) , t20.\[CategoryName\],
		GROUPING( t20.\[ProductName\] ), t20.\[ProductName\],
		TotalSoldAmount DESC

In learning to use the ROLLUP operator, Mark discovers that it also helps him avoid using temporary tables and provides good performance and extensibility. He can use the ROLLUP operator to analyze the product sales by different columns. He can also compute summaries at different levels. For example, he can get sales totals by supplier for the 20 top-selling products. The following is the complete version of the SELECT statement with the required totals, ordering, and labeling of the summary rows:

USE NorthWind
SELECT CASE
		WHEN GROUPING( t20.\[CategoryName\] ) = 1 THEN '** 
		Grand Total'
		WHEN GROUPING( t20.\[ProductName\] )  = 1 THEN '* Total ' 
		+ t20.
\[CategoryName\]
		ELSE t20.\[CategoryName\]
		END AS Category ,
		COALESCE( t20.\[ProductName\], ' ) AS Product ,
		SUM( t20.Orders ) AS TotalOrders ,
		SUM( t20.SoldAmount ) AS TotalSoldAmount
FROM (
	SELECT TOP 20 c.\[CategoryName\], p.\[ProductName\] ,
		SUM( od.\[Quantity\] ) ,
		SUM( od.\[Quantity\] * od.\[UnitPrice\] * 
		(1 - od.\[Discount\]) ) AS SoldAmount
		FROM \[Order Details\] AS od
		JOIN \[Products\] AS p
		  ON p.\[ProductID\] = od.\[ProductID\]
		JOIN \[Categories\] AS c
		  ON c.\[CategoryID\] = p.\[CategoryID\]
	GROUP BY c.\[CategoryName\], p.\[ProductName\]
	ORDER BY SoldAmount DESC
) AS t20( CategoryName, ProductName, Orders, SoldAmount )
GROUP BY t20.\[CategoryName\], t20.\[ProductName\]
WITH ROLLUP
ORDER BY GROUPING( t20.\[CategoryName\] ) , t20.\[CategoryName\] ,
	GROUPING( t20.\[ProductName\] ) , t20.\[ProductName\] ,
	TotalSoldAmount

July Challenge

Now, test your SQL Server savvy in the July Reader Challenge, "Devising a View" (below). Submit your solution in an email message to [email protected] by June 21. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here’s the challenge: Ian is the database developer for several SQL Server 2000 and 7.0 installations. As part of his job managing the company’s sales database, he controls security by enforcing Sales and Manager database roles. The database holds two tables: a sales force login table called SalesUser and a sales assignment table called Items that contains a separate list for each salesperson. First, Ian created the login table with relevant columns as follows:

CREATE TABLE SalesUser (
UserID int PRIMARY KEY ,
UserName varchar( 128 ) NOT NULL DEFAULT ( SYSTEM_USER ) 
UNIQUE CLUSTERED
)

Then, Ian created the sales assignment table with relevant columns as follows:

CREATE TABLE Items (
ItemID int IDENTITY PRIMARY KEY,
ItemDate datetime DEFAULT( CURRENT_TIMESTAMP ),
Comment varchar( 255 ) NULL,
UserID int REFERENCES SalesUser
)

Help Ian design a view called UserItems that

• lists only the assignments for each salesperson
• uses the existing tables
• lets the salespeople access their assignments from a simple Microsoft Access application. (The sales staff should be able to view, insert, edit, or delete any of their assignments.)
• prevents sales employees who are SQL Server savvy from adding, modifying, or deleting data that doesn't apply to them
• lets managers access and modify all sales force data
• grants the permissions that the following code snippet shows:

GRANT ALL PRIVILEGES ON UserItems TO Manager, Sales

Corrections to this Article:

• We’ve replaced the original solution to the June Reader Challenge with a more streamlined version. We apologize for any inconvenience this replacement might have caused.