Execute the following script in Query Editor to create a purchase order statistical summary:

USE AdventureWorks;
GO

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),

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

JOIN Person.Contact AS CON

ON CON.ContactID = EMP.ContactID

JOIN HumanResources.Employee AS MGR

ON EMP.ManagerID = MGR.EmployeeID

JOIN Person.Contact AS CONMGR

ON CONMGR.ContactID = MGR.ContactID

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