asked
in particular month of the year how many New employee joining and same month how many employee left company
Hi Please can any one help me I have one table name as Empmaster in that table i have columns Emp_Joinindate ,Emp_leavingdate,Emp_Status my question is Suppose the company hired 100 employee in the month of January 2012 and September 2012 50 employee left from the company and October 2012 again company hired 25 new employees here i am passing month and year soo i want show this data like how many employee presently working (Active Emp)and and how many Employee left the company on particular month of year suppose if i pass month=jan and year =2012 PresentWorkingEmp=100 and leftEmp=0 suppose if pass month=sept and year =2012 PresentWorkingEmp=50 and leftEmp=50 suppose if pass month=October and year =2012 PresentWorkingEmp=75 and leftEmp=0 because (in month oct company hired 25 so 50+25=75 leftEmp=0) please can any one help me
try this DECLARE @year int, @month int SELECT @year = 2012, @month = 9 SELECT PresentWorkingEmp = COUNT(CASE WHEN Emp_leavingdate IS NULL THEN 1 END), HiredEmp = COUNT(CASE WHEN Emp_Joinindate >= dateadd(month, @month - 1, dateadd(year, @year - 1900, 0)) AND Emp_Joinindate < dateadd(month, @month, dateadd(year, @year - 1900, 0)) THEN 1 END), LeftEmp = COUNT(CASE WHEN Emp_leavingdate >= dateadd(month, @month - 1, dateadd(year, @year - 1900, 0)) AND Emp_leavingdate < dateadd(month, @month, dateadd(year, @year - 1900, 0)) THEN 1 END) FROM Empmaster WHERE Emp_Joinindate < dateadd(month, @month, dateadd(year, @year - 1900, 0)) OR Emp_leavingdate < dateadd(month, @month, dateadd(year, @year - 1900, 0))
15 People are following this question.
