Learn how to write an SQL query to retrieve the latest pay rate and department for employees. This tutorial covers key SQL techniques such as JOIN, GROUP BY, and ORDER BY to efficiently fetch the most recent pay rate data. Whether you’re a beginner or an experienced SQL user, this guide will help you optimize your queries and improve database performance. 1. Fetching the Latest Salary 💡 Latest Pay CTE: • Uses ROW_NUMBER () to order salary records per employee by RateChangeDate DESC. • Filters the latest salary record (WHERE lp.rn = 1). WITH LatestPay AS ( SELECT eph.BusinessEntityID, eph.Rate, ROW_NUMBER () OVER (PARTITION BY eph.BusinessEntityID ORDER BY eph.[RateChangeDate] DESC) AS rn FROM HumanResources.EmployeePayHistory eph ) Why needed? Without it, we’d have to write an inline subquery inside the JOIN, making it more complex. ________________________________________ 2. Fetching the Latest Department 💡 LatestDepartment CTE: • Uses ROW_NUMBER() to order department history per employee by StartDate DESC. • Ensures we only fetch the most recent department for each employee. LatestDepartment AS ( SELECT edh.BusinessEntityID, edh.DepartmentID, ROW_NUMBER() OVER (PARTITION BY edh.BusinessEntityID ORDER BY edh.StartDate DESC) AS rn FROM HumanResources.EmployeeDepartmentHistory edh ) Why needed? Employees may have worked in multiple departments. Without this CTE, we’d get multiple records per employee. ________________________________________ Final Query with CTEs WITH LatestPay AS ( SELECT eph.BusinessEntityID, eph.Rate, ROW_NUMBER () OVER (PARTITION BY eph.BusinessEntityID ORDER BY eph.[RateChangeDate] DESC) AS rn FROM HumanResources.EmployeePayHistory eph ), LatestDepartment AS ( SELECT edh.BusinessEntityID, edh.DepartmentID, ROW_NUMBER() OVER (PARTITION BY edh.BusinessEntityID ORDER BY edh.StartDate DESC) AS rn FROM HumanResources.EmployeeDepartmentHistory edh ) SELECT p.BusinessEntityID, p.FirstName + ‘,’ + p.LastName AS FullName, Lp.Rate AS [Emp Latest Sal], e.JobTitle, d.Name AS DepartmentName FROM Person.Person p JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID JOIN LatestPay lp ON p.BusinessEntityID = lp.BusinessEntityID AND lp.rn = 1 JOIN LatestDepartment ld ON p.BusinessEntityID = ld.BusinessEntityID AND ld.rn = 1 JOIN HumanResources.Department d ON ld.DepartmentID = d.DepartmentID ORDER BY p.BusinessEntityID ASC; ________________________________________ Key Benefits of Using CTEs Here 1. Better Readability: o The logic is clean and separate, making it easier to understand. 2. Improved Performance: o Instead of filtering in multiple subqueries, the SQL engine processes CTEs first, reducing redundant calculations. 3. Reusability: o If we need the latest salary or department elsewhere, we can reuse the CTE logic.
