One more question/example.

If I have the following table:

EMPIDEffectiveDate PrimaryRater PrimaryReviewer

1234510/10/2001 A12345 A67890

1234507/12/2013 A12345 B67890

1234508/18/2002 A12345 C67890

1234507/17/1966 A12345 D67890

1234501/01/1966 B12345 E67890

Business rule(s):

1. The EffectiveDate is the sysdate of the update. It does not distinguish between the EffectiveDate of change for either the Primary Rater/Reviewer.

2. The only way to determine who was actually changed is to compare the IDs of the PrimaryRater and PrimaryReviewer at the time of the change with the previous PrimaryRater or PrimaryReviewer.

So, in this example the EffectiveDate of 01/01/1966 is the effective date of both Rater and Reviewer because there was no record prior. EffectiveDate 07/17/1966 is the EffectiveDate of a new set of Rater/Reviewers. Thus the Start and End dates of the previous are:

EMPIDStartDate PrimaryRater PrimaryReviewer EndDate

1234501/01/1966 B12345 E67890 07/17/1966

PrimaryRater A12345 has an EffectiveDate of 07/17/1966 but no EndDate. All other EffectiveDates are for the Reviewer not the Rater. Thus he has no EndDate and is still the most current Rater.

How would I create this table.

EMPIDEffectiveDate PrimaryRater PrimaryReviewer EndDate

1234507/17/1966 A12345 B67890 Current (No EndDate)

1234501/01/1966 B12345 E67890 07/17/1966

B67890 is the most Current Reviewer based on MAX date 07/12/2013

A12345 is the most Current Rater based on Start Date of 07/17/1966

B12345 & E67890 are the most Previous set based on EffectiveDate 01/01/1966 with no Previous sets.

The example you provided is excellent for gaining the date sets for Raters but not for adding in the most current Reviewer.

What modification would add? Thanks in advance for your considerations and help on this matter.