counting the years in multiple datetime values

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

General SQL Server Forums

New to SQL Server Programming

counting the years in multiple datetime values

Author

Topic

folen
Starting Member

5 Posts

Posted - 2007-07-19 : 03:41:26

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 :
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 :
select * from jobap
where -- or HAVING
JobAp.ind = JobApExp.JobAp AND
SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp)
thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 03:48:23

please post your table DDL, sample data and the result that you want

KH
[spoiler]Time is always against us[/spoiler]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-19 : 03:50:25

You have to take the min date of begin date and todays date if he is still working then do the following will give you the total year of experience(this give you an idea if he not having any break in his experience)

declare @dt1 datetime, @dt2 datetime

Set @dt1 = '09/13/2004'
Set @dt2 = '07/19/2007'
Select (1.00 * datediff(d, @dt1, @dt2))/365

--------------------------------------------------
S.Ahamed

folen
Starting Member

5 Posts

Posted - 2007-07-19 : 04:13:05

index PlaceName begingDate EndDate JobAp
20 place 1 12/09/2000 00:00:00 12/09/2002 00:00:00 56
21 place 2 12/09/2002 00:00:00 12/09/2004 00:00:00 56
22 olace 3 12/09/2004 00:00:00 12/09/2007 00:00:00 56
23 place 1 12/09/2000 00:00:00 12/09/2002 00:00:00 70
i want to get 7 or (7*365) (only the JobAp 56)

i want to do :

Select * from table1
where table1.ind = table2.JobAp AND "something here that counts the days/years (here it's 7 or 7*365)" > "number i put here"

it's not counting the diffrenc in one row that is the problem it's counting them to all the rows that have same JobAp number

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 04:21:44

[code]SELECT JobAp
FROM yourtable
GROUP BY JobAp
HAVING SUM(DATEDIFF(MONTH, begingDate, EndDate) / 12.0) > @Exp[/code]

KH
[spoiler]Time is always against us[/spoiler]

folen
Starting Member

5 Posts

Posted - 2007-07-19 : 04:56:50

1) thanks u very much looks like thats what i want
2) am stell having a problem if u can be bothered to help me:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

when i used it in my big ass query :


SELECT   
DISTINCT 
Jobap.ind, Apdate , Fname , Lname ,workstatus , forjob  
FROM 
Jobap, DriverLvl, JobApEdu, JobApExp, JobApHoby, JobApLang 
WHERE(Jobap.ind = DriverLvl.JobAp1 OR Jobap.ind = JobApEdu.JobAp1 OR Jobap.ind = JobApHoby.JobAp1 OR Jobap.ind = JobApLang.JobAp1)   AND 
Jobap.ind=
(SELECT 
jobap1 
FROM JobApExp  
GROUP BY 
jobap1 
HAVING 
SUM(DATEDIFF(MONTH, beginDate, EndDate) / 12.0) > 3 )

folen
Starting Member

5 Posts

Posted - 2007-07-19 : 05:00:00

fixed it

added
ANY

Jobap.ind= ANY

thank u very very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 05:15:55

quote:
FROM
Jobap, DriverLvl, JobApEdu, JobApExp, JobApHoby, JobApLang
WHERE(Jobap.ind = DriverLvl.JobAp1 OR Jobap.ind = JobApEdu.JobAp1 OR Jobap.ind = JobApHoby.JobAp1 OR Jobap.ind = JobApLang.JobAp1)

Wow ! this is messy. Why all the OR ? No wonder you need the distinct there.
Why don't you use ANSI JOIN ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 05:54:26

Have a look at this article to understand the DATEDIFF function
http://www.sqlteam.com/article/datediff-function-demystified

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources