Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Help with "selection"

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT Sum(diff)
    FROM   (
            SELECT tyears - fyears As diff
            FROM   WE
           ) As x
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so how do you think you can exclude the problem you have, or have had created for yourself by using a suspect physical design.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    My opinion is that you have to do it procedurally
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Add a where clause to the derived table to exclude current records.

    But to be honest - it's best if you redesign
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •