Measuring time between consecutive rows

[r0nniem]

Here some sample data. I wanting to measuring the time for each activity, eg. total time he was available, unavailable, oncall & aftercall each day. How do I do suggest I do it?

ID User Time Activity
3105590 eloc 12/07/06 06:36:27 AVAILABLE
3105599 eloc 12/07/06 06:38:09 UNAVAILABLE
3105621 eloc 12/07/06 06:45:54 AVAILABLE
3105625 eloc 12/07/06 06:52:06 ONCALL
3105627 eloc 12/07/06 06:52:38 AFTERCALL

 

[AlexCuse]

Have a look at the DateDiff function.

How is the code going to identify which is the previous record though (for subtraction purposes)?

One thought - you join the table to itself, and assign ranks based on how far apart the date fields are (make your join on a.Time <> b.Time so that you prevent the record from being joined to itself). Then bring back everything where the rank is one. This query will have your times side by side for easy calculation.

Hope this helps,

Alex

ps- I like your handle. I have a cat named Ron/Ronnie Mexico


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[rac2]

Consider adding a column to the table for TimeEnd. Insert a new actitivty row with TimeBegin. On the next insert, update that row with TimeEnd. The current row will always have a value for TimeBegin and NULL for TimeEnd, so it will be easy to find when the activity changes and a new activity row is inserted.

This extra work on the front end will vastly simplify the reporting where you wish to calculate elapsed time.

If you go with calculating a sequence for each User and searching for the previous row using DateDiff, the query will bog down as the table grows.