counting the years in multiple rows question

am making a CV program and i need a way to count the experience the user has:
i have his begin date and end Date as datetime in an sql server.
i can do it programicly but i prefer to do it at the sql side
the question:
how can i get how much exp he has aka :

Code Snippet

SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate ))

but for all the datarow
(he has more than one BeginDate and EndDate (for each job he has one))

P.S i want to be able to use it in a where clause :

Code Snippet

select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@.Exp)

thanks in advance

You have to use the grouping together with the having clause:

declare @.Exp as int

select max(A) as A, max(B) as B from jobap

where JobAp.ind = JobApExp.JobAp

HAVING SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > @.Exp

|||

create table #Personnel(id int,name varchar(20))
insert #Personnel select 1,'madhu'
insert #Personnel select 2 ,'zyx'

create table #PersonnelExp (ID int,fmdt datetime,todt datetime)
delete #PersonnelExp
insert #PersonnelExp select 1,'1990-1-1','1999-1-1'
insert #PersonnelExp select 1,'1999-1-2','2002-1-2'
insert #PersonnelExp select 1,'2003-1-2','2007-1-2'
insert #PersonnelExp select 2,'1995-1-1','1999-1-1'
insert #PersonnelExp select 2,'1999-1-2','2004-1-2'
insert #PersonnelExp select 2,'2005-1-2','2007-1-2'

select a.ID,a.Name,b.TotExp From #Personnel a,
(select id,SUM(DATEDIFF(year , fmdt, todt )) As TotExp from #PersonnelExp group by id) b
where a.ID=b.ID
and totexp>12

Check this script

Madhu

|||

You may want to consider performing the DATEDIFF on the day or month level, as the year shows the year difference between the two dates.

IE

select datediff(yy, '01/01/2006', '12/31/2006')

select datediff(yy, '12/31/2006', '01/01/2007')

The first returns 0, even though it's a full year. And the second returns 1 even though it's only been 1 day.