Query with aggregate. I'm stuck!

[rrhandle]

I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.


TableName: Employees

EmployeeID............Date.............PayRate
-------------------------------------------
....1001...............1/24/03............12.50
....1002...............2/28/03............12.75
....1003...............5/14/03............10.50
....1002...............3/15/04............15.00
....1001...............6/22/04............14.00
....1002...............3/16/05............14.00

The result set should look like:

EmployeeID...........Date.............PayRate
-----------------------------------------
...1003................5/14/03............10.50
...1001................6/22/04............14.00
...1002................3/16/05............14.00

 

[PHV]

SELECT A.EmployeeID, A.Date, A.PayRate
FROM Employees AS A INNER JOIN (
SELECT EmployeeID, Max([Date]) AS LastDate FROM Employees GROUP BY EmployeeID
) AS B ON A.EmployeeID = B.EmployeeID AND A.Date = B.LastDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rrhandle]

THANK YOU, PHV!
Been stuck on this for hours.
Works like a charm.