Max(), Top N() Or Rownum() Function

Background: SQL Server 2000 transactional database

Key fact 1: I have one main fact table and several dimension tables. for each join between a dimension table and the fact table, I have a combined key that includes at least three fields one of which would be a date field.
Key fact 2: Sometimes fields in the dimension tables changes names over time e.g. a department that was called dept_1 a few years ago might have changed its name since and is now called dept_A today. The historic report should still use today's name of the department.

For example using three tables (keys are colour coded)
DEPT table and JOB table and Employee table

DEPT_table JOB_table EMPLOYEE_table
DEPTID DEPTID EMPLID
SETID DEPT_SETID EMPL_RCD
EFFDT EFFDT
DESCR_LONG EFFSEQ
DESCR_SHORT EMPLID
EMPL_RCD

Since departments change names over time, I intend to return just the most current department name for use in my reports but one way or another SOME records return more than one. I am using max(effdt) to extract the most recent from two tables like the ones above.












Code Snippet
DEPT_TBL.DEPTID=BO_JOB_VW.DEPTID and DEPT_TBL.SETID=BO_JOB_VW.SETID_DEPT and
DEPT_TBL.EFFDT=(select max(j.EFFDT) from DEPT_TBL j where
j.EFFDT<=BO_JOB_VW.EFFDT and
j.DEPTID=BO_JOB_VW.DEPTID and
j.SETID=BO_JOB_VW.SETID_DEPT)


and




Code Snippet
BO_JOB_VW.EMPLID = EMPLOYEE.EMPLID
BO_JOB_VW.EMPL_RCD = EMPLOYEE.EMPL_RCD





Although it is possible for an employee to belong to more than one department simultaneously, what I have notice is that for some employees that get multiple rows of departments, they have never belonged to more than one department or ever changed departments at all.

e.g



Code Snippet
DEPT
/*
DEPTID SETID EFFDT DESCR
DP1 GBR 01/01/1901
DP1a GBR 01/01/1965
DP2 GBR 01/01/1996
DP2a GBR 01/01/1998
DP2b GBR 01/01/2006
*/
DP1 and DP1a refers to the same department but reflect a change of names
DP2, DP2a and DP2b refer to one department but also reflect changes in the name

JOB
/*
DEPTID SETID EFFDT EMPLID EMPL_RCD EFFSEQ STD_HRS
DP1 GBR 01/01/1901 001 0 0 37.0
DP1a GBR 01/01/1999 002 0 0 37.5
DP2 GBR 01/01/1996 003 0 0 18.75
DP2a GBR 01/01/1998 003 0 1 22.2
DP2b GBR 01/01/2006 002 0 3 37.0
*/

EMPLOYEE
/*
EMPLID EMPL_RCD
001 0
002 0
003 0
003 0
002 0
*/

THe report should therefore look something like

EMPLID DEPTID STD_HRS EFFDT
001 DP1 37.0 01/01/1901
002 DP2b 37.5 01/01/1999
002 DP2b 37.0 01/01/2006
002 DP2a 18.75 01/01/1996
002 DP2a 22.2 01/01/1998




If you need any further details please let me know
code suggestions would be welcome and greatly appreciated
thanks