Derived Field for Service Date 1

[DebChattin]

Does anyone know how to write a dervied field that will calcualte the service date of employees? In months and years.

Thanks.

 

[dlblack]

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 Black, CQA
Teepak, LLC

 

[DebChattin]

Thanks very much. Do you have this in SQL? This looks like Oracle. Am I right?

 

[dlblack]

DebChattin,

You are correct,we are an Oracle shop. I don't have this code converted, but there are just afew differences. It shouldn't be to difficult for you to change.

David Black, CQA
Teepak, LLC