Execute the following SQL Server T-SQL script in SSMS Query Editor to find the number of orders and last order date by sales department employees:

-- T-SQL LEFT OUTER JOIN with GROUP BY subquery - derived table - subselect

USE AdventureWorks;

SELECT   Employee = C.LastName + ', ' + C.FirstName,

         OE.NumOfOrders,

         OE.LastOrderDate,

         Manager = C1.LastName + ', ' + C1.FirstName,

         OM.NumOfOrders,

         OM.LastOrderDate

FROM     Person.Contact C1

         INNER JOIN HumanResources.Employee AS E1

           ON C1.ContactID = E1.ContactID

         INNER JOIN Person.Contact C

           ON 1 = 1

         INNER JOIN HumanResources.Employee AS E

           ON C.ContactID = E.ContactID

         INNER JOIN (SELECT   EmployeeID,

                        NumOfOrders = COUNT(* ),

                        LastOrderDate = MAX(OrderDate)

               FROM     Purchasing.PurchaseOrderHeader

               GROUP BY EmployeeID) AS OE

           ON E.EmployeeID = OE.EmployeeID

         LEFT OUTER JOIN (SELECT   EmployeeID,

                                   NumOfOrders = COUNT(* ),

                                   LastOrderDate = MAX(OrderDate)

                          FROM     Purchasing.PurchaseOrderHeader

                          GROUP BY EmployeeID) AS OM

           ON E.ManagerID = OM.EmployeeID

WHERE    E1.EmployeeID = E.ManagerID

ORDER BY C.LastName + ',' + C.FirstName

GO