Month wise employee joinee and exit

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)

Month wise employee joinee and exit

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-30 : 04:02:12

Dear All,
I am looking for a query which will give me number of employee join in month nad number of employee exit in same month
table trucure is:-
if employee join then u will get rfrom EMPLOYEE TABLE
EMPLOYEE(EMPID,DTEOFJoINING,DEPT,LOCATION)
If employee exit you will get details from
SEPERATION(EMPID,lastdayofworking)

i am looking a report output like

month-year numofjoineee numofexit location dept

thankx regrds,
Vipin jha

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-30 : 07:53:28

;with cte as
(
select yearmth = convert(datetime,convert(varchar(6),getdate(),112) + '01')
union all
select yearmth = dateadd(mm,-1,yearmth) where yearmth > '20100101'
) ,
emp as (select dte = convert(datetime,convert(varchar(6),DTEOFJoINING,112) + '01') from EMPLOYEE) ,
sep = (select dte = convert(datetime,convert(varchar(6),lastdayofworking,112) + '01') from SEPERATION)
select cte.yearmth, numjoin = d1.num, numexit = d2.num
from cte c
left join (select dte, num = count(*) from emp group by dte) d1
on cte.yearmth = d1.dte
left join (select dte, num = count(*) from sep group by dte) d2
on cte.yearmth = d2.dte

Then you just need to add the location - will need to join to employee in the separation cte to get that value.
Do you want zeros in the result for locations? In that case create a locations cte and cross join to it in the result.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources