SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Calculate two values that are each on a separate row.

    Hi all, I have a requirement where I need to make a calculation that makes use of two value where each value is in a different row.

    For example, if I have the following three columns I want the two rows that have the same ID and for the row that has the value 912 in the Ref column minus the number in the Value column from the number in the same column (Value) that has the same ID but has 911 in the Ref column.

    Code:
       ID       Ref       Value      
    2674198   911	5.01374
    2674198	  912	3.78280
    2674199	  911	3.61368
    2674199	  912	8.85212
    (using SQL Server 2000)

    Does anyone know how I might get started with this?

    Thanks

    Tryst
    Last edited by Tryst; Jan 23, 2006 at 08:37. Reason: Did include what DB engine I was using.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select t911.id
         , t912.value - t912.value as diff
      from yourtable as t911
    inner
      join yourtable as t912
        on t911.id = t912.id
     where t911.ref = 911
       and t912.ref = 912
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, and thanks for the reply Rudy, but is this correct?...

    t912.value - t912.value as diff

    Shouldn't one of them be t911?

    Thanks

    Tryst

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    of course, you are right, my fat fingers

    t912.value - t911.value as diff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol - still ain't worn off the Christmas over indulgence then ;-)

    Tryst

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    people don't put on weight between christmas and new year's -- people put on weight between new year's and christmas

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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