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 the top salaried employee for each department?

Execute the following SQL Server T-SQL script in Query Editor to find the name of the top wage earner by department. Nested CTEs used to establish the last pay rate from EmployeePayHistory.

USE AdventureWorks;

 

WITH cteLastDate as

(SELECT EmployeeID, LastDate= max(RateChangeDate)

 FROM HumanResources.EmployeePayHistory

 GROUP BY EmployeeID ),

 cteLastRate as

 ( SELECT eph.EmployeeID, Rate

 FROM HumanResources.EmployeePayHistory eph

 JOIN cteLastDate cte

      ON eph. EmployeeID=cte. EmployeeID

 and eph.RateChangeDate = cte.LastDate)

 

SELECT Result='Top in ' + d.name + ' is ' +

         c.LastName + ', ' + c.FirstName, eph.Rate

FROM HumanResources.Department d

JOIN HumanResources.EmployeeDepartmentHistory edhmain

      ON d.DepartmentID = edhmain.DepartmentID

JOIN cteLastRate eph

      ON eph. EmployeeID = edhmain. EmployeeID

JOIN HumanResources.Employee emain

      ON edhmain. EmployeeID = emain. EmployeeID

JOIN Person.Contact c

      ON emain.ContactID = c.ContactID

WHERE EndDate is NULL

      and eph.Rate = (SELECT MAX (Rate)

FROM cteLastRate eph

JOIN HumanResources.Employee e

      ON eph.EmployeeID = e.EmployeeID

JOIN HumanResources.EmployeeDepartmentHistory edh

      ON edh.EmployeeID = e.EmployeeID

WHERE EndDate is NULL

      and edh.DepartmentID = edhmain.DepartmentID);
/*
Result                                                Rate
Top in Tool Design is Walters, Rob                    29.8462
Top in Marketing is Bradley, David                    37.50
Top in Engineering is Duffy, Terri                    63.4615
Top in Production Control is Krebs, Peter             24.5192
Top in Human Resources is Barreto de Mattos, Paula    27.1394
Top in Information Services is Trenary, Jean          50.4808
Top in Finance is Kahn, Wendy                         43.2692
Top in Shipping and Receiving is Ackerman, Pilar      19.2308
Top in Document Control is Arifin, Zainal             17.7885
Top in Executive is Sánchez, Ken                      125.50
Top in Production is Hamilton, James                  84.1346
Top in Research and Development is Miller, Dylan      50.4808
Top in Quality Assurance is Abolrous, Hazem           28.8462
Top in Facilities and Maintenance is Altman, Gary     24.0385
Top in Sales is Welcker, Brian                        72.1154
Top in Purchasing is Word, Sheela                     30.00
*/

 

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