question

yogeesha avatar image
yogeesha 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
sql2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel avatar image
Squirrel answered
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))
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanku sir its working
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.