How to get number of employees per year

Question

I have a table called Mst_Employee. The fields are:

Emp_No | Emp_Name | Emp_JoiningDate | Emp_ResignedDate | Emp_Status

How do I get the No. of Employees by year for each year somebody joined or resigned? (Joined and Resigned includes by year)

E.g. result should look like this:

Year   No. of Employees.
------------------------
2011   125
2012   130
2013   100

please share sample data of your table. if an employee has not resigned yet then its Emp_ResignedDate will be null or you don't have such records in your table? — Deep, Jun 18 '15 at 06:13
What if the same person was joined and resigned the same year? — Giorgi Nakeuri, Jun 18 '15 at 07:41
Thanks Deepak, Yes if the employee yet not resigned then Emp_ResignedDate will be null — user3069097, Jun 18 '15 at 08:00
Thanks Giorgi. If employee joined or resigned in same year or other year then count as (one) — user3069097, Jun 18 '15 at 08:01

Zohar Peled · Answer 1 · 2015-06-18T07:32:03.603

One way to solve it is with a recursive cte and group by:

DECLARE @FromYear int, @ToYear int

SELECT @FromYear = YEAR(MIN(Emp_JoiningDate)),
       @ToYear = YEAR(GETDATE())
FROM Mst_Employee

;WITH CTE AS 
(
    SELECT @FromYear As TheYear
    UNION ALL
    SELECT TheYear + 1
    FROM CTE
    WHERE TheYear < @ToYear
)

SELECT TheYear as [Year], 
       COUNT
       (
       CASE WHEN TheYear <= YEAR(COALESCE(Emp_ResignedDate, GETDATE())) THEN 
           1 
       END
       ) As [No. of Employees.]
FROM CTE
INNER JOIN Mst_Employee ON(TheYear >= YEAR(Emp_JoiningDate))
GROUP BY TheYear

See fiddle here

score 0 · Answer 2 · answered Jun 18 '15 at 07:47

0

You can achieve this with:

select y as [Year], count(*) as [No. of Employees.]  
from(select Emp_No, YEAR(Emp_JoiningDate) as y from Mst_Employee
     union 
     select Emp_No, YEAR(Emp_ResignedDate) from Mst_Employee 
     where Emp_ResignedDate is not null)t
group by y

answered Jun 18 '15 at 07:47

Giorgi Nakeuri

35,155
8
47
75

Thanks Giorgi for your query i add slightly as per my structure and got the result select y as [Year], count(*) as [No. of Employees.] from(select Emp_No, YEAR(Emp_JoiningDate) as y from Mst_Employee where Emp_ResignedDate is null union select Emp_No, YEAR(Emp_ResignedDate) from Mst_Employee where Emp_ResignedDate is not null)t group by y – user3069097 Jun 18 '15 at 08:20
Dear Giorgi, Another thing i'm looking for the Columns to be display Year, No. of Joined and No. of resign please help on this. – user3069097 Jun 30 '15 at 05:19
Thanks for your query. Just what I needed – Fokwa Best Jun 07 '19 at 15:27

How to get number of employees per year

2 Answers2

Linked