Calculate two values that are each on a separate row.

[Tryst]

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.

   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

[r937]

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

[Tryst]

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

[r937]

of course, you are right, my fat fingers

t912.value - t911.value as diff

[Tryst]

lol - still ain't worn off the Christmas over indulgence then ;-)

Tryst

[r937]

people don't put on weight between christmas and new year's -- people put on weight between new year's and christmas