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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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