Length of Service based on Hire Date by Job Assignment

The Length of Service based on Hire Date by Job Assignment report uses the HR Profile topic to show a list of active employees with their current length of service based on their hire date. The records are grouped by job assignment.

Note: If multiple managers are allowed, the report might return multiple records for each employee.

Fields

Fields included in the report template

Field Name	Group by	Sort Order	Totals
Job Assignment	Yes	Ascending
Location	Yes	Ascending
Employee		A to Z
Length of Service
Hire Date
Status
Term. Date
Department
Manager
Assigned Sex
Base Rate
Pay Class
Pay Group

Custom Fields

Length of Service

The Length of Service custom field displays the employee’s length of service according to the hire date. It uses the following expression:

CASE WHEN  HireDate < GETDATE()
THEN CASE WHEN  HireDate < TerminationDate
THEN CONVERT(NVARCHAR(10), FLOOR((DATEDIFF(DAY,  HireDate, TerminationDate) / 365.0))) + ' Years ' + CONVERT(NVARCHAR(10),
CASE WHEN
CASE WHEN DATEPART(dd,  HireDate) <= DATEPART(dd, TerminationDate)
THEN DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY, HireDate, TerminationDate) / 365.0),  HireDate), TerminationDate)
ELSE DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, TerminationDate) / 365.0),  HireDate), TerminationDate) - 1
END = -1 THEN 0
ELSE
CASE WHEN DATEPART(dd,  HireDate) <= DATEPART(dd, TerminationDate)
THEN DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, TerminationDate) / 365.0), HireDate), TerminationDate)
ELSE DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, TerminationDate) / 365.0),  HireDate), TerminationDate) - 1
END
END)
ELSE CONVERT(NVARCHAR(10), FLOOR(DATEDIFF(DAY,  HireDate, GETDATE()) / 365.0)) + ' Years ' + CONVERT(NVARCHAR(10),
CASE WHEN
CASE WHEN DATEPART(dd,  HireDate) <= DATEPART(dd, GETDATE())
THEN DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, GETDATE()) / 365.0),  HireDate), GETDATE())
ELSE DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, GETDATE()) / 365.0),  HireDate), GETDATE()) - 1
END = -1 THEN 0
ELSE
CASE WHEN DATEPART(dd,  HireDate) <= DATEPART(dd, GETDATE())
THEN DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, GETDATE()) / 365.0),  HireDate), GETDATE())
ELSE DATEDIFF(MONTH, DATEADD(YEAR, FLOOR(DATEDIFF(DAY,  HireDate, GETDATE()) / 365.0),  HireDate), GETDATE()) - 1
END
END)
END + ' Months'
ELSE ''
END

Filters

The template includes the following filters: