Calculate difference between successive records in the same field

  • Thread starter Thread starter FlexNigel
  • Start date Start date
F

FlexNigel

I can use either version 2003 or 2007.
I have a field in a table which contains timestamps for each line item. I
want to determine what is the difference between each successive time stamp
so I can calculate the time it is taking to complete the process step i.e. I
want to calculate the difference between the value in record 2 and record 1,
the difference between record 3 and record 2, and so on.
 
FlexNigel said:
I can use either version 2003 or 2007.
I have a field in a table which contains timestamps for each line item. I
want to determine what is the difference between each successive time stamp
so I can calculate the time it is taking to complete the process step i.e. I
want to calculate the difference between the value in record 2 and record 1,
the difference between record 3 and record 2, and so on.
Click to expand...


You can use a subquery. E,g,

SELECT ts, (SELECT Max(X.ts)
FROM tbl As X
WHERE X.ts < tbl.ts
) As delta
FROM tbl

You could use an equivalent DMax function, but it's usually
slower than a subquery.

It may be faster to use a somewhat convoluted join instead
of a subquery, but I really don't want to go into it because
your question indicates that it would cause you more trouble
than it's worth.
 
Back
Top