Help with "selection"

mcnika · #1 (**permalink**) 08-18-09, 11:24

Suppose I have a table

ID userName fyears tyears
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

gvee · #2 (**permalink**) 08-18-09, 12:05

Code:

SELECT Sum(diff)
FROM   (
        SELECT tyears - fyears As diff
        FROM   WE
       ) As x

mcnika · #3 (**permalink**) 08-18-09, 13:25

Quote:

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