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 (YYYYEmp_JoiningDate)=@min DECLARE @resignedempcount int SELECT @resignedempcount= COUNT (emp_no) from Mst_Employee where DATEPART (YYYYEmp_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-3JoinDate) I think and you can count records with a CTE e.g. with EmployeeStartFinYear(FinYear EmployeeId) as ( select year(dateadd(M-3JoinDate)) 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