DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply CTE for purchase order reporting?

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE