CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user3069097
user3069097

Reputation: 59

How to get number of employees per year

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

Upvotes: 0

Views: 2842

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 1

Related Questions