I would suggest:

1. Using a Common Table Expression (CTE), get all the values of the table. Utilize the row_number function to assign row numbers to each row, starting at one for each employeeid, and ordering by the date descending, putting this value into a new column (RN).

2. Select the columns from the CTE where the new column RN = 1.

For assistance in figuring out how to use the row_number function, see this article[/url].