Selecting the correct row in a table which records changes 1

[aalnaif]

I have an "employees" table which records which department each employee has worked in, and the date that each employee started working in their departments. Since it is possible for an employee to change departments, a single employee can have several records in this table. For example, consider the following history for Employee 42:
1. On Jan. 15, 2007, Employee 42 is hired to work in Department 21
2. On Feb. 1, 2007, Employee 42 changes departments to Department 5
3. On Feb. 14, 2007, Employee 42 changes departmets to Department 22
4. On Mar. 3, 2007, Employee 42 changes departments to Department 18

Then, Employee 42's records in the "employees" table would look like this:

select * from employees where employee_id = 42;

employeeID | departmentID | start_date
42 21 2007-01-15
42 5 2007-02-01
42 22 2007-02-14
42 18 2007-03-03

Does anyone know of an efficient way of telling which department an employee worked for on a given day?

 

[r937]

select employeeID 
     , departmentID 
     , start_date
  from employees as T
 where start_date =
       ( select max(start_date)
           from employees
          where employeeID = T.employeeID 
            and start_date <= '2007-02-28' ) 
   and employeeID = 42

r937.com | rudy.ca