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 HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find orders taken by sales department employees?

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

Employee NumOfOrders LastOrderDate Manager NumOfOrders LastOrderDate
Hagens, Erin 361 9/3/2004 Word, Sheela 160 9/1/2004
Hee, Gordon 360 9/3/2004 Word, Sheela 160 9/1/2004
Hill, Annette 362 9/3/2004 Word, Sheela 160 9/1/2004
Hillmann, Reinout 401 9/3/2004 Word, Sheela 160 9/1/2004
Kurjan, Eric 360 9/3/2004 Word, Sheela 160 9/1/2004
Meisner, Linda 400 9/3/2004 Word, Sheela 160 9/1/2004
Miller, Ben 360 9/3/2004 Word, Sheela 160 9/1/2004
Ogisu, Fukiko 362 9/3/2004 Word, Sheela 160 9/1/2004
Pellow, Frank 361 9/3/2004 Word, Sheela 160 9/1/2004
Rao, Arvind 164 10/23/2004 Word, Sheela 160 9/1/2004
Sandberg, Mikael 361 9/3/2004 Word, Sheela 160 9/1/2004
Word, Sheela 160 9/1/2004 Kahn, Wendy NULL NULL

 

Exam Prep 70-461
Exam 70-461
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