Execute the following 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 salaried employee 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);