|
Execute the following
Microsoft SQL Server T-SQL script in Management Studio Query Editor to create a purchase order statistical
summary stored procedure by using a CTE - Common Table Expression.
The CTE, ctePurchase, yields GROUP BY aggregates for employees on purchase count, last purchase date and total dollar volume.
USE AdventureWorks;
-- SQL Server CTE - GROUP BY CTE - INNER JOIN - LEFT OUTER JOIN
-- Common Table Expression - Structured Programming
-- CTE declaration with columns - Aggregate functions: COUNT, MAX
CREATE PROC uspPurchaseStats
AS
WITH ctePurchase(PurchasePersonID,NoOfPurchases,LastDate,Total)
AS (SELECT EmployeeID,
COUNT(* ),
MAX(ModifiedDate),
Total = CONVERT(MONEY,SUM(TotalDue))
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID)
SELECT Employee = CON.FirstName + ' ' + CON.LastName,
NoOfPurchases = cteEMP.NoOfPurchases,
Total = '$' + CONVERT(VARCHAR,cteEMP.Total,1), -- Currency formatting
LastPurchaseDate = CONVERT(CHAR(10),cteEMP.LastDate,110),
Manager = CONMGR.FirstName + ' ' + CONMGR.LastName,
MgrNoOfPurchases = ISNULL(cteMGR.NoOfPurchases,0),
MgrTotal = '$' + CONVERT(VARCHAR,ISNULL(cteMGR.Total,0.0),1),
MgrLastPurchaseDate = ISNULL(CONVERT(CHAR(10),cteMGR.LastDate,110),'')
FROM HumanResources.Employee AS EMP
INNER JOIN Person.Contact AS CON
ON CON.ContactID = EMP.ContactID
INNER JOIN HumanResources.Employee AS MGR
ON EMP.ManagerID = MGR.EmployeeID
INNER JOIN Person.Contact AS CONMGR
ON CONMGR.ContactID = MGR.ContactID
INNER JOIN ctePurchase AS cteEMP
ON EMP.EmployeeID = cteEMP.PurchasePersonID
LEFT OUTER JOIN ctePurchase AS cteMGR
ON EMP.ManagerID = cteMGR.PurchasePersonID
ORDER BY LastPurchaseDate DESC,
Employee;
GO
EXEC uspPurchaseStats
GO
/* Partial results
Employee NoOfPurchases Total LastPurchaseDate
Annette Hill 362 $5,788,769.16 09-12-2005
Arvind Rao 164 $2,978,027.37 09-12-2005
Erin Hagens 361 $5,556,272.23 09-12-2005
Frank Pellow 361 $6,552,648.57 09-12-2005
*/
------------
Related articles:
SQL Server – Multiple CTE in One SELECT Statement Query
Common Table Expressions (CTE) on SQL 2005
|
