How to get number of employees per year

Question

Dear,

I have a table called Mst_Employee.Field are

Emp_No
Emp_Name
Emp_JoiningDate
Emp_ResignedDate
Emp_Status

How to get the No. of Employees by Year.
Each year somebody join or resigned.

E.g. Result look like this
Year-----No. of Employees.
2011---- 125
2012-----130
2013-----100

Please help on this
Thanks
Basit.

Govinda Rajulu Yemineni · Answer

Hi Basit Khan, Try this CREATE PROCEDURE USP_GETNoOfEmpYearWise AS BEGIN CREATE TABLE #Employee([Year] varchar (50),[No of Employees] int ) DECLARE @min int SELECT @min= DATEPART (YYYY, MIN (Emp_JoiningDate)) from Mst_Employee DECLARE @max int SELECT @max= DATEPART (YYYY, GETDATE ()) DECLARE @temp int SELECT @temp=@min WHILE (@min BEGIN DECLARE @joinedempcount int SELECT @joinedempcount= COUNT (emp_no) from Mst_Employee where DATEPART (YYYY,Emp_JoiningDate)=@min DECLARE @resignedempcount int SELECT @resignedempcount= COUNT (emp_no) from Mst_Employee where DATEPART (YYYY,Emp_ResignedDate)=@min DECLARE @totalemp int SELECT @totalemp=@joinedempcount-@resignedempcount IF @min > @temp BEGIN SELECT @totalemp=@totalemp+( SELECT [No of Employees] FROM #Employee WHERE [Year]=@min-1) END DECLARE @year int SET @year=@min INSERT INTO #Employee VALUES (@year,@totalemp) SELECT @min=@min+1 END SELECT * FROM #Employee END EXEC USP_GETNoOfEmpYearWise

Raja T · Answer

Hi Basit Khan, For get the No. of Employees by Year. Each year somebody join. Try this bellow code select DATEPART(YYYY, e.Emp_JoiningDate)AS [YEAR],count(emp_no)as[No. of Employees] from Mst_Employee e group by DATEPART(YYYY, e.Emp_JoiningDate) For get the No. of Employees by Year. Each year somebody resigned. Try this bellow code select DATEPART(YYYY, e.Emp_ResignedDate)AS [YEAR],count(emp_no)as[No. of Employees] from Mst_Employee e group by DATEPART(YYYY, e.Emp_ResignedDate) Please find the attached screen shot for your reference. Thanks.

Narasimha Reddy Chennupalli · Answer

You can map start date to year using YEAR(DATEADD(M,-3,JoinDate) I think and you can count records with a CTE, e.g. with EmployeeStartFinYear(FinYear, EmployeeId) as ( select year(dateadd(M,-3,JoinDate)), EmployeeId from Employees where JoinDate is not null ) select FinYear, count(EmployeeId) from EmployeeStartFinYear group by FinYear order by FinYear; Note: The above example shows the employee count by financial year.

Pankaj Kumar Choudhary · Answer

hello basit you can use this method SELECT [YEAR] , COUNT(Emp_No) AS [No. of Employees.] FROM ( SELECT e.Emp_No, DATEPART(YYYY, e.Emp_JoiningDate)AS [YEAR] FROM dbo.Employees e )EMP this query give you count of all employee that working and resigned from company. if This query is not as you except then please Reply me i will write again

How to get number of employees per year

Answers (4)