Get end date as effective date of previous record
<p>
Good Morning.
I have a table as follows:
employee_id
position_number
effective_date
The primary key is (employee_id,position_number)
An employee within an organization can hold several positions during their tenure, but hold only one position at a time.
for example,
employee 1, position 10, effective 1/1/07
employee 1, position 17, effective 2/3/08
employee 1, position 5, effective 6/1/08
I need to return the previous position's end date, if possible:
employee 1 held position 5 from 6/1/08 to present
employee 1 held position 17 from 2/3/08 to 5/31/08
employee 1 held position10 from 1/1/07 to 2/2/08
I have no clue how to do this. Can anyone help, please?
</p>