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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find the employee's and manager's last order date?

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

 

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