Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

pdshare

Technical User
Jan 31, 2005
1
US
I'm looking for a derived field (SQL) that will show years, days and months 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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top