Help me to write this query

Hi Everybody,
I have a table call Employee like this
EmpId DOB Sex
1 01/02/1982 Male
2 01/01/1983 Female

And also i have got Contacts Table looks like this
ConId EmpId ConDate Description
1 1 02/02/2007 Not Specified
2 1 02/03/2007 Not Specified
3 2 01/01/2007 Personal

Now i need to display like this

Description Male Female Age 0-10 Age 10+
Not Specified 1 0 0 1
Personal 0 1 0 1

When calculation the age If one employee has got more than one contacts then we need to get the maximum ConDate from the Contacts table and then get the DOB from the Employee table and and get the DateDiff by year

How can can i do this query ?
Any Idea ?

regards
suis

Here You go..

Code Snippet

Create Table #employee (

[EmpId] int ,

[DOB] datetime ,

[Sex] Varchar(100)

);

Insert Into #employee Values('1','01/02/1982','Male');

Insert Into #employee Values('2','01/01/1983','Female');

--Completed :: employee

--Sample Table :: employeecontact

Create Table #employeecontact (

[ConId] int ,

[EmpId] int ,

[ConDate] datetime ,

[Description] Varchar(100)

);

Insert Into #employeecontact Values('1','1','02/02/2007','NotSpecified');

Insert Into #employeecontact Values('2','1','02/03/2007','NotSpecified');

Insert Into #employeecontact Values('3','2','01/01/2007','Personal');

--Using SQL Server 2000

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join (Select

EC.EmpID,EC.Description

From

#employeecontact EC

Join (

Select

[EmpId]

,Max([ConDate]) ConDate

From

#employeecontact

Group By [EmpId]

) as Data On Data.ConDate =EC.ConDate and Data.EmpID = EC.EMPID) C On E.EmpID=C.EmpID

--Using SQL Server 2005

;With CTE as

(

Select EmpId,Description, ROW_NUMBER() Over (Partition By EmpId Order By [ConDate] Desc) RowId From #employeecontact

)

Select

Description,

Case When Sex='Male' Then 1 Else 0 End Male,

Case When Sex='Female' Then 1 Else 0 End Female,

Case When Datediff(YY,DOB,getdate()) <=10 Then 1 Else 0 End [Age 0-10],

Case When Datediff(YY,DOB,getdate()) >10 Then 1 Else 0 End [Age 10+]

from

#employee E

Join CTE C On E.EmpId = C.EmpId

Where

C.RowID=1

|||

Code Snippet

create table #emp

( EmpId int, DOB datetime, Sex varchar(10))

insert into #emp values (1, '01/02/1982', 'Male')

insert into #emp values (2, '01/01/1983', 'Female')

insert into #emp values (3, '10/01/1999', 'Female')

create table #contacts

( ConId int, EmpId int, ConDate datetime, Description varchar(100))

insert into #contacts values (1, 1, '02/02/2007', 'Not Specified')

insert into #contacts values (2, 1, '02/03/2007', 'Not Specified')

insert into #contacts values (3, 2, '01/01/2007', 'Personal')

insert into #contacts values (3, 3, '5/01/2007', 'Personal')

;with contact1 as

(

select c.EmpId, c.Description, max(c.ConDate) as ConDate

from #contacts c

group by c.EmpId, c.Description

),

contact2 as

(

select c.EmpId, c.Description, c.ConDate, e.Sex,

datediff(yy, e.dob, c.ConDate)

+ case when (month(e.dob)*100)+day(e.dob)>(month(c.ConDate)*100)+day(c.ConDate)

then -1 else 0

end as Age

from contact1 c

inner join #emp e

on c.EmpId = e.EmpId

)

select Description,

sum(case Sex when 'Male' then 1 else 0 end) as Male,

sum(case Sex when 'Female' then 1 else 0 end) as Female,

sum(case when Age <=10 then 1 else 0 end) as 'Age 0-10',

sum(case when Age > 10 then 1 else 0 end) as 'Age 10+'

from contact2 c

group by Description

|||Hi Manivannan.D.Sekaran
Many Many thanks for your quick response.this is great help for me,
i will have a look this query and let u know ASAP the result,cos this query is bit tau ff for me,

again thank you so much for your valuable response.

this is great forum.....i love this forum........

regards
suis
|||hi Dale and Sekaran
Its very useful u r comments to sort out my problem
now my query is working perfectly,i had to do some modification for that,becase my database structure is not like as i post ,becase its just sample database format,

anyway thanks for the quick response from you two .

many thanks to .Net forum......nice work

regards
suis