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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

HRGen

Technical User
Jul 11, 2007
1
US
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
 
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

Specializing in ReportSmith Training and Consulting
 
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 ') )
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top