SQL-Derived Field for Year, Months, and days of Service

There is a sample of this type of report in ADP's samples called annivers.rpt. You should be able to use a derived field for length of service like the following: (displays a field of years and months from the system date)

TRUNC(((TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(DECODE("REPORTS"."V_EMPLOYEE"."REHIREDATE",NULL,"REPORTS"."V_EMPLOYEE"."HIREDATE","REPORTS"."V_EMPLOYEE"."REHIREDATE"),'YYYY')) * 12 + ((TO_CHAR(SYSDATE,'MM') - TO_CHAR(DECODE("REPORTS"."V_EMPLOYEE"."REHIREDATE",NULL,"REPORTS"."V_EMPLOYEE"."HIREDATE","REPORTS"."V_EMPLOYEE"."REHIREDATE"),'MM'))-1))/12) || ' yr(s) and ' ||ROUND(MOD((TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(DECODE("REPORTS"."V_EMPLOYEE"."REHIREDATE",NULL,"REPORTS"."V_EMPLOYEE"."HIREDATE","REPORTS"."V_EMPLOYEE"."REHIREDATE"),'YYYY')) * 12 + ((TO_CHAR(SYSDATE,'MM') - TO_CHAR(DECODE("REPORTS"."V_EMPLOYEE"."REHIREDATE",NULL,"REPORTS"."V_EMPLOYEE"."HIREDATE","REPORTS"."V_EMPLOYEE"."REHIREDATE"),'MM')) -1),12),0) || ' mo(s)'

David L. Black Consulting