Happy New Year!
I receive an annual payroll report each year that includes the following fields:
- Employee ID
- Job Title
- Hire Date
- Termination Date
- Annual Hours
One of my tasks is to identify potential replacement employees for terminated positions/employees based on the following criteria:
- The employee is active (no termination date)
- The employee was hired within four months before or after the termination date of the terminated employee
- The job title of the replacement employee is similar to that of the terminated employee...e.g., Registered Nurse, Registered Nurse II, RN, etc.
- *This criteria would be preferred in the workflow but may add too much complexity* If the replacement employee was hired prior to the terminated employee (but within the 4-month window), the annual hours must be prorated (reduced) based on the day difference between the termination date and hire date multiplied by 5. Then, the prorated hours of the replacement employee plus the hours of the terminated employee must be equal to or greater than 1,820 (35hrs/week) to be considered a true replacement.
Can anyone think of a workflow that could identify/mark potential replacement employees for terminated positions based on the above criteria?
The multi-row tool comes to mind, but I need to be able to query all possibilities. There could be multiple potential replacements for one terminated employee. I'd like to be able to identify and aggregate all possibilities per terminated employee.
I've attached a small manually created sample report that includes the various scenarios encountered for testing.
Thanks in advance for any help/advice.
