Compare row's timestamp with next row's timestamp

Archived Topics
1

Here is the lowdown:
I’m putting together some reports on data that FSQL is collecting for us. I need to get the time from one event to the next using the timestamps. I think it can be done in a SQL statement rather than a stored procedure.

The table has an index, t_stamp, and MachineState columns. In my report, I will be taking the amount of time spent in each state and summing them, which means I need to do a DATEDIFF on the current row’s timestamp and the next row’s timestamp to find the amount of time spent in each state. The last state can be ignored, I take care of that elsewhere.

I know what I want but I just can’t get it out of my brain! :scratch: Any ideas?

2

You do this by using the old join-a-table-to-itself trick. Like so:

SELECT DATEDIFF(A.t_stamp, B.t_stamp) FROM MyTable A JOIN MyTable B ON A.index=B.index-1
3

Indeed. It has been a while since I wrote any queries, so as the knowledge started coming back to me the answer pretty much slapped me in the face. Thanks for your help!