I am trying to calculate the length of service of our employees within our Oracle database. Below is a table with some example data for two employees. The calculation would be from the first HIREDATE to the subsequent TERMDATE and then the next REHIREDATE to the next TERMDATE and so on. These will be active employees so the last TERMDATE would be calculated against SYSDATE.
The outcome I’m looking for is the total length of service for each employee in years and months. EMPNO 10166 = 18 years 2 months and EMPNO 06680 = 18 years 6 months.
| EMPNO | HIREDATE | TERMDATE | REHIREDATE |
|---|---|---|---|
| 10166 | 05-FEB-05 | 15-SEP-07 | 17-JAN-08 |
| 10166 | 05-FEB-05 | 24-MAR-14 | 16-AUG-14 |
| 10166 | 05-FEB-05 | 23-AUG-16 | 06-FEB-17 |
| 06680 | 21-SEP-98 | 20-MAR-08 | 13-JUL-09 |
| 06680 | 21-SEP-98 | 17-OCT-11 | 29-AUG-16 |
| 06680 | 21-SEP-98 | 27-JUL-17 | 24-SEP-18 |