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

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

In previous articles i have explained How to count employees based on their years of experience and How to remove duplicate records/data from sql table and Delete records from table using inner join in sql server and Difference between temporary table and table variable in sql and How to pass table name as parameter to stored procedure or query

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 months of experience

GO

DECLARE @Date DATE=GETDATE();

SELECT DATEDIFF(MM, DateofJoining, @Date) AS ExperienceInMonths, COUNT(EMP.EmployeeId) AS NoOfEmployees

FROM

(          

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)AS EMP

GROUP BY DATEDIFF(MM, DateofJoining, @Date)

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

Result will be as:

ExperienceInMonths	NoOfEmployees
242	1
235	1
234	1
233	1
231	1
182	1
180	2
177	1

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

GO

DECLARE @CurrentDate DATE=GETDATE();

;WITH empCTE

AS

(

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)

,expCTE AS

(

SELECT DATEDIFF(MM, DateofJoining, @CurrentDate) AS ExperienceInMonths, COUNT(EmployeeId) AS TotalNoOfEmployees

FROM empCTE

GROUP BY DATEDIFF(MM, DateofJoining, @CurrentDate)

)

SELECT * FROM expCTE ORDER BY ExperienceInMonths DESC

Result will be same as above result.

Similarly we can also group employee by their days of experience

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

GO

DECLARE @Date DATE=GETDATE();

SELECT DATEDIFF(DD, DateofJoining, @Date) AS ExperienceInDays, COUNT(EMP.EmployeeId) AS NoOfEmployees

FROM

(          

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)AS EMP

GROUP BY DATEDIFF(DD, DateofJoining, @Date)

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

Result will be as: 

ExperienceInDays	NoOfEmployees
7383	1
7171	1
7132	1
7110	1
7032	1
5565	1
5499	1
5496	1
5399	1
5258	1
5246	1
5225	1
3702	1
3634	1
3592	1
3514	1
3511	1
3485	1
3330	1
3273	1
2813	1
2118	1
1993	1
1952	1
1936	1
1722	1
623	1

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

GO

DECLARE @CurrentDate DATE=GETDATE();

;WITH empCTE

AS

(

SELECT EmployeeId,DateOfJoining FROM #tbEmployee

)

,expCTE AS

(

SELECT DATEDIFF(DD, DateofJoining, @CurrentDate) AS ExperienceInDays, COUNT(EmployeeId) AS TotalNoOfEmployees

FROM empCTE

GROUP BY DATEDIFF(DD, DateofJoining, @CurrentDate)

)

SELECT * FROM expCTE ORDER BY ExperienceInDays 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