1. Registered User
Join Date
Aug 2009
Posts
2

Suppose I have a table

1 nika 2007 2009
2 nika 2005 2009
3 nika 2000 2005
4 nika 1999 2000
5 nika 1996 1997

fyears is when the user nika started to work, tyears-ended
I need to select the total work experience, now it is =11

SELECT sum(tyears-fyears) FROM WE
I did in this way but it works inappropriately

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
SELECT Sum(diff)
FROM   (
SELECT tyears - fyears As diff
FROM   WE
) As x

3. Registered User
Join Date
Aug 2009
Posts
2
Originally Posted by gvee
Code:
SELECT Sum(diff)
FROM   (
SELECT tyears - fyears As diff
FROM   WE
) As x

the result is 13 but it must be 11

2005-2009 is 4 year work experience
2000-2005 is 5 year work experience
1999-2000 is 1 year work experience
1996-1997 is 1 year work experience

2007-2009 must not be calculated, because they are exists in 2005-2009

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so how do you think you can exclude the problem you have, or have had created for yourself by using a suspect physical design.

5. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
575
My opinion is that you have to do it procedurally

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445