I have an employee table something like this:
Emp# Name Salary Grade Effective Date
1001 Tom Baker G2 06/04/2002
1002 Judy Smith 9L 05/19/2003
1003 John Phillips D4 02/12/2001
1003 John Phillips A7 10/01/2003
1004 Rob Thomas ZZ 04/23/2002
I am trying to write a formula that will select the latest employee record determined by the effective date. However, if an employee has a record with an effective date in the future (i.e. employee 1003), I want the record with the effective date that has already past. In other words, for employee # 1003 I want to select the record with 02/12/2001 effective date until 10/01/2003, at which point I want to select the record with the 10/01/2003 effective date.
Thanks in advance for the knowledge!
TimothyP
Emp# Name Salary Grade Effective Date
1001 Tom Baker G2 06/04/2002
1002 Judy Smith 9L 05/19/2003
1003 John Phillips D4 02/12/2001
1003 John Phillips A7 10/01/2003
1004 Rob Thomas ZZ 04/23/2002
I am trying to write a formula that will select the latest employee record determined by the effective date. However, if an employee has a record with an effective date in the future (i.e. employee 1003), I want the record with the effective date that has already past. In other words, for employee # 1003 I want to select the record with 02/12/2001 effective date until 10/01/2003, at which point I want to select the record with the 10/01/2003 effective date.
Thanks in advance for the knowledge!
TimothyP