Length of Service based on Hire Date by Location

The Length of Service based on Hire Date by Location report uses the HR Profile topic to display a list of active employees with their current length of service based on their hire date. The records are grouped by location.

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
Location	Yes	Ascending
Employee		A to Z
Length of Service
Hire Date
Sen. Date
Status
Term. Date
Department
Job Assignment
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: