SQLUSA

Microsoft SQL Server 2005 Best Practices

How to find the employee's and manager's last order date?

 

Execute the following script in Query Editor to find the last order date and the number of orders for the employee and his/her manager:

USE AdventureWorks;

SELECT SalesStaff=C.LastName+', '+ C.FirstName, OE.NoOfOrders,
OE.LastOrderDate,
Manager=CM.LastName+', '+ CM.FirstName, OM.NoOfOrders, OM.LastOrderDate
FROM Person.Contact AS C JOIN HumanResources.Employee AS E
ON C.ContactID=E.ContactID
JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OE(EmployeeID, NoOfOrders, LastOrderDate)
ON E.EmployeeID = OE.EmployeeID
LEFT OUTER JOIN
(SELECT EmployeeID, COUNT(*), MAX(OrderDate)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID) AS OM(EmployeeID, NoOfOrders, LastOrderDate)
ON E.ManagerID = OM.EmployeeID
JOIN HumanResources.Employee AS EM ON OM.EmployeeID=EM.EmployeeID
JOIN Person.Contact AS CM
ON EM.ContactID=CM.ContactID
ORDER BY Manager, SalesStaff



 

The World Leader in SQL Server Training
 
SQLUSA.com Home Page