How to get number of employees per year

Basit Khan

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Govinda Rajulu Yemineni11y ago
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<=@max)
   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
0
Raja T11y ago
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.

0
Narasimha Reddy Chennupalli11y ago
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.
0
Pankaj Kumar Choudhary11y ago
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
0