Using a Scalar Function to Calculate Employee Seniority

In one of my previous blog posts, I explained how to calculate employee seniority using the features of MS Excel (e.g. VLOOKUP and Yearfrac). In this blog post, I want to demonstrate how to do the same thing using SQL Server Management Studio. I will use a Scalar Function in order to perform this task. A Scalar Function is a function that operates on and returns a single value. 

STEP 1]

The first step is to make sure that the AdventureWorks database is being used by executing the code below in Management studio:

USE AdventureWorks2012 
GO 
​
STEP 2]
As could be observed in the code below, I have created a function named “dbo.Seniority”. This function takes one parameter (i.e. @ST) and returns a VARCHAR(100). Note that the parameter name(s) must be enclosed in parenthesis and must start with @. 

Create function dbo.Seniority (@ST datetime)   
Returns VARCHAR (100)
AS
BEGIN
DECLARE @EMP int
SELECT @EMP =DATEDIFF(yy, @ST, Getdate())
RETURN (CONVERT (varchar(100), @EMP) + ' - ' + CONVERT(varchar (100), @EMP +2))
END
GO

This function passes in a date (only the year). The function then classifies the seniority in two year groupings. To have this done, I first created a parameter named @EMP. This parameter uses the DATEDIFF command to find the difference bewteen the employees' Hire Date that and the current date).

STEP 3] Execution
In order to use the code which we created in Step 2, we need to use a select statement which will extract data from the HumanResources table. Note that this code calls the "dbo.Seniority" function and then pass in the "HireDate" for  each employee and this parameter will eventually return the string which was defined in Step 2.

SELECT LoginID, BirthDate,SalariedFlag,MaritalStatus,hiredate,dbo.Seniority([HireDate]) as 'Seniority Range (Years)'
FROM [HumanResources].[Employee]
Order by hiredate asc
GO

STEP 4] Counting the number of employees which fall under each seniority range

SELECT dbo.Seniority (HireDate) as 'Seniority Range (Years)', Count (*) as 'Number of Employees'
FROM [HumanResources].[Employee]
GROUP BY dbo.Seniority(HireDate)
Order by 'Number of Employees' desc
GO