Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Unanswered: TSQL math between consecutive rows

    when selecting data from this huge table I have one of the fields is an accumulator. I want to retrieve the difference between 2 records that are consecutive in the time field. how please?

    example, if the data looked like this:
    time, machine_id,data
    1,1,5
    1,2,7
    2,1,10
    2,2,13
    3,1,13
    3,2,15

    I'd want when selecting on machine 1:
    time, machine_id,diff
    1,1,5 (5-null I guess)
    2,1,5 (10 - 5)
    3,1,3 (13 - 10)
    without the stuff in parens

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    There might be a more efficient way, but this is quick and dirty:

    select a.[time],a.machine, (select a.data - b.data from tab as b where a.machine = b.machine and b.[time] = a.[time] - 1) from tab as a

  3. #3
    Join Date
    Oct 2002
    Posts
    5

    works, thanks

    yes, that works, thanks a million

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Happy to help.

Posting Permissions

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