I am trying to setup an HR database that will tell me when an annual review is due for each employee. Here’s the trick: Some employees have left and returned, thus setting their review back to compensate for time lost. For example, If an employeeis coming up on one year, it would be offset by three months if they took a three-month leave of absence (LOA) earlier.
I currently have all employees and statistical data in one table, I have all employee actions (hire, terminations, re-instatements, LOA’s, etc..) in another table linked by their employee number.
I also need to be able to see if they are even a current employee by checking for a termination without a re-instatement, or if they are employed, but not active because they are on LOA.
How can I look through the list of dates per employee number to see if they have a termination date, then check for a re-hire date, then calculate their time less the time they were gone, then check for multiple instances thereof?