I have a table say XX_EXPENSES which has record of all employee expenses. The columns are person_id, expense_amount, category and expense item.

I have a master table where all the employee names are stored i.e. XX_PEOPLE. The join between the tables are based on person_id. but its not unique because it also stores the effective date of his designations
e.g.

person_id ,full_name, start_date, end_date, designation
---------------------------------------------------------------------------
1 ABC 01-JAN-2012 01-AUG-2012 ENGINEER
1 ABC 02-AUG-2012 01-SEP-2012 SR. ENGINEER
1 ABC 02-SEP-2012 01-JAN-4072 MANAGER

so when I will make an association or view link it would be many to many but I need to put a condition that join the person_id only where the SYSDATE or current date between start_date and end_date so that it only shows the current designation. How and where I can put such condition?