Discrete Derivative in SQL

Question

I've got sensor data in a table in the form:

Time      Value
10        100
20        200
36        330
46        440

I'd like to pull the change in values for each time period. Ideally, I'd like to get:

Starttime Endtime   Change
10        20        100
20        36        130
36        46        110

My SQL skills are pretty rudimentary, so my inclination is to pull all the data out to a script that processes it and then push it back to the new table, but I thought I'd ask if there was a slick way to do this all in the database.

Answer 1

Select a.Time as StartTime
     , b.time as EndTime
     , b.time-a.time as TimeChange
     , b.value-a.value as ValueChange
FROM YourTable a 
Left outer Join YourTable b ON b.time>a.time
Left outer Join YourTable c ON c.time<b.time AND c.time > a.time
Where c.time is null
Order By a.time