SQL Server: Count employees based on their years of experience | Get Experience wise employee summary.

Introduction: In this article I am going to share how to count employees based on their years of experience or we can say query to get experience wise employee summary or we can say grouping staff by their years of experience.

In previous articles i have explained How to count male, female and total employees and Query to count department wise male, female and total employees and Get created or modified date of tables, stored procedures, views and functions and Query to delete or select first or last n records from table and Query to split string from comma and get left and right part

Implementation: Let’s demonstrate by an example.

Create a temporary table for holding employee data using following script.

IF OBJECT_ID('tempdb..#tbEmployee') IS NOT NULL

DROP TABLE #tbEmployee

GO

CREATE TABLE #tbEmployee

(

EmployeeId                INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,

EmployeeName         VARCHAR(100),

DateOfJoining           DATE

);

Query to insert dummy data into employee table.

GO

INSERT INTO #tbEmployee

VALUES

('Aman','2018-07-16'),

('Rohan','2010-08-19'),

('Reetika','2000-10-11'),

('Ruhi','2005-03-13'),

('Varun','2011-02-16'),

('aaliya','2012-07-17'),

('Salman','2005-11-18'),

('Kareena','2011-04-14'),

('Aamir','2010-05-30'),

('Sonali','2000-08-11'),

('Katrina','2010-02-09'),

('Shahrukh','2005-01-03'),

('Abhinav','2014-12-11'),

('Aarushi','2000-12-28'),

('Sarika','2014-11-25'),

('Rajnish','2000-09-19'),

('Vishal','2015-07-13'),

('Shubham','2010-04-18'),

('Manish','2005-03-10'),

('Rohul','2014-06-12'),

('Vijay','2005-06-18'),

('Vikram','2000-01-12'),

('Ranbir','2010-09-14'),

('Anil','2014-10-15'),

('kapil','2005-11-06'),

('Shweta','2005-12-09'),

('Anuj','2010-08-16');

Query to view employee table.

GO

SELECT * FROM #tbEmployee

EmployeeId	EmployeeName	DateOfJoining
1	Aman	2018-07-16
2	Rohan	2010-08-19
3	Reetika	2000-10-11
4	Ruhi	2005-03-13
5	Varun	2011-02-16
6	aaliya	2012-07-17
7	Salman	2005-11-18
8	Kareena	2011-04-14
9	Aamir	2010-05-30
10	Sonali	2000-08-11
11	Katrina	2010-02-09
12	Shahrukh	2005-01-03
13	Abhinav	2014-12-11
14	Aarushi	2000-12-28
15	Sarika	2014-11-25
16	Rajnish	2000-09-19
17	Vishal	2015-07-13
18	Shubham	2010-04-18
19	Manish	2005-03-10
20	Rohul	2014-06-12
21	Vijay	2005-06-18
22	Vikram	2000-01-12
23	Ranbir	2010-09-14
24	Anil	2014-10-15
25	kapil	2005-11-06
26	Shweta	2005-12-09
27	Anuj	2010-08-16

Using simple query  with COUNT and GROUP BY to group employee by their years of experience

GO

DECLARE @Date DATE=GETDATE();

SELECT DATEDIFF(YY, DateofJoining, @Date) AS ExperienceInYears, COUNT(EMP.EmployeeId) AS NoOfEmployees

FROM

(          

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)AS EMP

GROUP BY DATEDIFF(YY, DateofJoining, @Date)

ORDER BY DATEDIFF(YY, DateofJoining, @Date) DESC

Result will be as:

ExperienceInYears	NoOfEmployees
20	5
15	7
10	6
9	2
8	1
6	4
5	1
2	1

Using CTE with COUNT and GROUP BY to group employee by their years of experience

GO

DECLARE @CurrentDate DATE=GETDATE();

;WITH empCTE

AS

(

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)

,expCTE AS

(

SELECT DATEDIFF(YY, DateofJoining, @CurrentDate) AS ExperienceInYears, COUNT(EmployeeId) AS TotalNoOfEmployees

FROM empCTE

GROUP BY DATEDIFF(YY, DateofJoining, @CurrentDate)

)

SELECT * FROM expCTE ORDER BY ExperienceInYears DESC

Result will be same as above result.

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post