How to find total No. of Employees Hvg Work Exp>

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 2005 Forums

Transact-SQL (2005)

How to find total No. of Employees Hvg Work Exp>

Author

Topic

mvijayrkumar
Starting Member

6 Posts

Posted - 2008-12-20 : 07:13:35

i Have a table named "Demographics" which contain the Employee Details such as Employee Code ,HireDate

of which "HireDate" is Employee Hire Date in (mm/dd/yyyy,12:00:00 AM format).The table also contained

the resigned Employee Details.

Demographics Table
EmpCode HireDate
-------- ---------

ATA01 12/03/2006
ATA02 12/03/2006
ATA04 12/03/2006
ATA03 12/03/2006

I have another table named "Employee End Of Service" which has the fields Employee Code,(EOSDate)End Of Service Date.

Employee End Of Service Table

EmpCode HireDate
-------- ---------

ATA01 12/10/2007

I want to find the Total Number of Employees having Working Span of
>10yrs
7-10yrs,
4-6yrs,
1-3yrs,
<1yr

First we have to check whether any data exists in Employee End Of Service" Table,if yes means we can

calculate ((EOSDate from "Employee End Of Service") -(HireDate from Demographics))
if "No" means (CurrentDate-HireDate)..

SQL Query Results Should be

Range Total Number of Employees
----- --------------------
>10 2
7-10 4
4-6 6
1-3 9
< 1 12

Guys please help me how to do this in SQL Server 2005.....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-20 : 08:58:00

[code]create table #Demographics(
EmpCode char(5),
HireDate datetime
)

create table #Employee_End_Of_Service(
EmpCode char(5),
EOSDate datetime
)

insert #Demographics
select 'ATA01', '12/03/2006' union all
select 'ATA02', '12/03/2006' union all
select 'ATA04', '12/03/1999' union all
select 'ATA03', '12/03/2006' union all
select 'ATA05', '12/03/1980' union all
select 'ATA06', '12/03/2004' union all
select 'ATA07', '12/03/1995'

insert #Employee_End_Of_Service
select 'ATA01', '12/10/2007' union all
select 'ATA02', '12/10/2006' union all
select 'ATA07', '12/10/1999'

select * from #Demographics
select * from #Employee_End_Of_Service

-- here begins the solution
select
'> 10' as Range,
count(*) as [Total Number of Employees]
from
#Demographics d
left join #Employee_End_Of_Service e
on (d.EmpCode = e.EmpCode)
where
datediff(year,
d.HireDate,
isnull(e.EOSDate,dateadd(d,datediff(d,0,getdate()),0))
) > 10
union all
select
'7 - 10' as Range,
count(*) as [Total Number of Employees]
from
#Demographics d
left join #Employee_End_Of_Service e
on (d.EmpCode = e.EmpCode)
where
datediff(year,
d.HireDate,
isnull(e.EOSDate,dateadd(d,datediff(d,0,getdate()),0))
) between 7 and 10
union all
select
'4 - 6' as Range,
count(*) as [Total Number of Employees]
from
#Demographics d
left join #Employee_End_Of_Service e
on (d.EmpCode = e.EmpCode)
where
datediff(year,
d.HireDate,
isnull(e.EOSDate,dateadd(d,datediff(d,0,getdate()),0))
) between 4 and 6
union all
select
'1 - 3' as Range,
count(*) as [Total Number of Employees]
from
#Demographics d
left join #Employee_End_Of_Service e
on (d.EmpCode = e.EmpCode)
where
datediff(year,
d.HireDate,
isnull(e.EOSDate,dateadd(d,datediff(d,0,getdate()),0))
) between 1 and 3
union all
select
'< 1' as Range,
count(*) as [Total Number of Employees]
from
#Demographics d
left join #Employee_End_Of_Service e
on (d.EmpCode = e.EmpCode)
where
datediff(year,
d.HireDate,
isnull(e.EOSDate,dateadd(d,datediff(d,0,getdate()),0))
) < 1

-- here ends the solution

drop table #Demographics
drop table #Employee_End_Of_Service
[/code]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

mvijayrkumar
Starting Member

6 Posts

Posted - 2008-12-21 : 08:15:31

HI webfred...

i really thank and appreciate u for the effort u had taken to solve the issue..
The Query worked am really happy and greateful to u....
im a new member to sqlteam..hope will meet u soon..

Thanks once again..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-21 : 10:32:21

you are welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources