|
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
*/
|
