|
Execute the following
Microsoft SQL Server T-SQL SELECT query in SSMS Query Editor to find the last order date and the number
of orders for the employee and his/her manager:
-- T-SQL get last order date by salesperson
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
| SalesStaff |
NoOfOrders |
LastOrderDate |
Manager |
NoOfOrders |
LastOrderDate |
| Hagens, Erin |
12/26/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Hee, Gordon |
12/25/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Hill, Annette |
12/27/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Hillmann, Reinout |
2/4/1901 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Kurjan, Eric |
12/25/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Meisner, Linda |
2/3/1901 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Miller, Ben |
12/25/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Ogisu, Fukiko |
12/27/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Pellow, Frank |
12/26/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Rao, Arvind |
6/12/1900 |
10/23/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| Sandberg, Mikael |
12/26/1900 |
9/3/2004 |
Word, Sheela |
6/8/1900 |
9/1/2004 |
| 
