Years of Service derived field report - HELP!!!!!

[HRGen]

Ok, I'm an HR Generalist with limited Reportsmith experience. I've been tasked with the following:

Create a "Terminations Tenure" report to determine the following:

How many years of service did our termed employees give us?

I need to calculate total years, months and days between date of hire and date of termination OR between date of REhire and date of termination.

I know there is a way to do this, and I know it's probably the most basic of reports, but I haven't a clue.

Can anyone out there help me write one?

Thanks,

HR Gen

 

[CharlesCook]

You would do this using a SQL Derived Field. The best way it to do it one step at a time.

Create a report with the very basic information in it.

Create a SQL Derived Field

Double click into the derived field the data field date of hire

Type in a - sign

Double click in the data field date of termination

Run the report

You will find the answer is in days

Edit the Derived Field

Put (( at the start of the formula

Put ) / 365.25) at the end of the formula

run the report

get back to me when you get to this point

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[RSHelp]

You didn't indicate what product you are using. If you are using ADP PC/Payroll for Windows with an SQLBase database then create a report with at least Hire Date, Termination Date and Rehire Date from the V_EMPLOYEE view (they can be Query Only if you want) and then create a Derived Field called whatever you want (like LOS) and copy and paste the following into it:

@IF( @INT( @DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) / @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)) ) ,
@IF( @INT( @MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) / @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)) ) ,
@STRING(@YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @YEARNO(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' y ' || @STRING(@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' m ' || @STRING(@DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' d ' ,
@STRING( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @YEARNO(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)) - 1,0) || ' y ' || @STRING(12 + (@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE))),0) || ' m ' || @STRING(@DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' d ' ) ,
@IF( @INT( @MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) / @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)) ) ,
@DECODE(@MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW)),@MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),
@STRING(@YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @YEARNO(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)) - 1,0) || ' y ' || @STRING(12 + (@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)))-1,0) || ' m ' || @STRING( @DECODE(@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - 1,1,31,2,
@IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,4) ,28, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,100) ,29, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,400) ,28,29) ) ),
3,31,4,30,5,31,6,30,7,31,8,31,9,30,10,31,11,30,0,31)+ @DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' d ',
@STRING(@YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @YEARNO(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' y ' || @STRING((@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)))-1,0) || ' m ' || @STRING( @DECODE(@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - 1,1,31,2,
@IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,4) ,28, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,100) ,29, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,400) ,28,29) ) ),
3,31,4,30,5,31,6,30,7,31,8,31,9,30,10,31,11,30,0,31)+ @DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' d '),
@STRING((@YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @YEARNO(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE))) - 1,0) || ' y ' || @STRING(12 + (@MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @MONTH(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)))-1,0) || ' m ' || @STRING(@DECODE( @MONTH( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - 1,1,31,2,
@IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,4) ,28, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,100) ,29, @IF( @MOD( @YEARNO( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) ,400) ,28,29) ) ),
3,31,4,30,5,31,6,30,7,31,8,31,9,30,10,31,11,30,0,31)+ @DAY( @NULLVALUE(REPORTS.V_EMPLOYEE.TERMINATIONDATE, @NOW) ) - @DAY(@NULLVALUE(REPORTS.V_EMPLOYEE.REHIREDATE, REPORTS.V_EMPLOYEE.HIREDATE)),0) || ' d ') )