Length of Service based on Hire Date by Country

The Length of Service based on Hire Date by Country 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 country.

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 Country	Yes	A to Z
Location	Yes	Ascending
Employee		A to Z
Length of Service
Hire Date
Seniority 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
Location Country
The Location Country custom field displays the employee’s location country. It uses the following expression:
CASE WHEN OrgUnit.CountryCode = OrgUnit.CountryCode THEN OrgUnit.CountryCode ELSE 'Unassigned Locations' END

Filters

The template includes the following filters: