monthwise total employee count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

monthwise total employee count

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-11 : 04:43:11

Dear All ,
I am looking for a query where i can get month wise total number of employee
suppose i have 100 employee in mar-12
and 15 new joiend in same month so in april i should have 115 employee

my query is

select SUM(a.total) emp from (
select count(emp_staffid) total,
left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2) "Month-Year"
from ERM_EMPLOYEE_MASTER
where EMP_ISACTIVE like '%1%'
group by left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2)
)a
where a.[Month-Year]='apr-12'

which is not giving me exact output

regards,
Vipin jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-11 : 05:11:43

[code]-- Prepare staging table
CREATE TABLE #Stage
(
MonthNumber INT,
Joined INT
)

-- Populate staging table
INSERT #Stage
(
MonthNumber,
Joined
)
SELECT DATEDIFF(MONTH, '19000101', Emp_DateOfJoining) AS MonthNumber,
COUNT(*) AS Joined
FROM dbo.Erm_Employee_Master
WHERE Emp_IsActive = 1
GROUP BY DATEDIFF(MONTH, '19000101', Emp_DateOfJoining)

-- Display the final result
SELECT SUBSTRING(CONVERT(VARCHAR(9), DATEADD(MONTH, s.MonthNumber, '19000101'), 6), 4, 6) AS MonthName,
f.Joined
FROM #Stage AS s
CROSS APPLY (
SELECT SUM(x.Joined)
FROM #Stage AS x
WHERE x.MonthNumber <= s.MonthNumber
) AS f(Joined)
ORDER BY s.MonthNumber

-- Clean up
DROP TABLE #Stage[/code]

N 56°04'39.26"
E 12°55'05.63"

vipinjha123
Starting Member

45 Posts

Posted - 2012-06-11 : 07:22:56

Dear friend thankx for your reply

my query is

SELECT TABA.* FROM (
select a.[Month-Year] "Year-Month of joinee/Exit",
SUM(a.[Number of joined Empl]) "Count of joined Emp" ,
sum(a.[left emp]) as "Count of Exit emp"
from (
select
left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2) "Month-Year",
count(EMP_DATEOFJOINING) as "Number of joined Empl",0 as "left emp"
from erm_employee_master left join SEP_EMPLOYEE_SEPARATION on SEP_EMPLOYEE_SEPARATION.ES_EMP_STAFFID=erm_employee_master.EMP_STAFFID
WHERE EMP_ISACTIVE='1'
group by
left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2)

UNION ALL

select
left(DATEname(mm,ES_LAST_WORKING_DATE),3)+'-'+right(convert(varchar,DATEPART(yy,ES_LAST_WORKING_DATE)),2) "Month-Year",
0,count(*) as "Number of exit Empl"
from SEP_EMPLOYEE_SEPARATION inner join ERM_EMPLOYEE_MASTER on ERM_EMPLOYEE_MASTER.EMP_STAFFID=SEP_EMPLOYEE_SEPARATION.ES_EMP_STAFFID
where ERM_EMPLOYEE_MASTER.EMP_ISACTIVE='0'
group by
dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0),left(DATEname(mm,ES_LAST_WORKING_DATE),3)+'-'+right(convert(varchar,DATEPART(yy,ES_LAST_WORKING_DATE)),2)

)a
where a.[Month-Year] IN ('JAN-12','FEB-12','MAR-12','APR-12','MAY-12')
group by a.[Month-Year]
)TABA
order by TABA.[Year-Month of joinee/Exit] desc

i want to see data on ascenbding order(month-year) wise

like jun-12
may-12
apr-12
mar-12

i am unable to get it
please suggest

regards,
vipin jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 15:42:48

for getting data in order you want add ORDER BY like

ORDER BY DATEPART(yy,datefield) DESC,DATEPART(mm,datefield) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources