Elapsed time between rows

[tcb]

How can a query be written that will tell me how much time elapsed
between any two items?

For example, how much time elapsed between test1 and test4?

xAN xTime xText
1 9/22/2007 3:29:15 AM test
2 9/22/2007 3:29:17 AM test1
3 9/22/2007 3:29:20 AM test2
4 9/22/2007 3:29:22 AM test3
5 9/22/2007 3:29:26 AM test4
6 9/22/2007 3:29:29 AM test5

 

[Allen Browne]

Use a subquery to get the nearest prior time

If subqueries are new, here's an example:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Tom Wickerath]

Here is one possibility, if you are looking for total elapsed time and you
can always count on the earliest time having an xAN value = 1:

SELECT xAN, xTime, xText,
Nz(DateDiff("s",DLookUp("xTime","data2","[xAN] =" & [xAN]-1),[xTime]),0)
AS [Delta T (sec)],
DateDiff("s",DLookUp("xTime","data2","[xAN] = 1"),[xTime])
AS [Elapsed Time (sec)]
FROM data2;


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

Another possible way:

First paste the following function into a standard module in the database.
This accepts two date/time values and returns the duration between them as a
string in the format hh:nn:ss, or # days hh:nn:ss if True is passed into the
function as the optional blnShowDays parameter:

Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double

dblDuration = dtmTo - dtmFrom

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function

Call the function in a query which joins two instances of the table like so:

SELECT TimeDuration(T1.xTime, T2.xTime) AS [Elapsed Time]
FROM YourTable AS T1, YourTable AS T2
WHERE T1.xTest = [Enter first test name:]
AND T2.xTest = [Enter second test name:];

You could of course call the DateDiff function rather than the above
function if you simply want the elapsed time in seconds rather than formatted.

When the query, or any form or report based on it is run you'll be prompted
to enter the test names. This assumes, of course, that the xTest column
contains unique values (and therefore indexed uniquely). If running the
query from a form you could use two combo boxes listing the tests with a
RowSource for each of:

SELECT xTest FROM YourTable ORDER BY xTest;

and reference these as the parameters in the query, avoiding the need to
type in the names:

SELECT TimeDuration(T1.xTime, T2.xTime) AS [Elapsed Time]
FROM YourTable AS T1, YourTable AS T2
WHERE T1.xTest = Forms!YourForm!cboFirstTest
AND T2.xTest = Forms!YourForm!cboSecondTest;

If the test names are not unique then you could use combo boxes which have
the xAN column as a hidden bound column and which list whatever other columns
are necessary for you to select a particular test. The query's WHERE clause
would then be on the xAN columns from the two instances of the table, not the
xTest columns.

Ken Sheridan
Stafford, England

 

[tcb]

Use a subquery to get the nearest prior time

If subqueries are new, here's an example:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

These were all good answers. I tried Allen Browne's suggestion using
a subquery. Is it possible to do this by parameters comparing two
rows like test1 and test4 within a subquery. I'd like to avoid
creating a function or another query. This is what I have so far:

SELECT atom.xAN, atom.xStartTime, atom.xText, (SELECT TOP 1
Dupe.xStartTime
FROM atom AS Dupe
WHERE Dupe.xStartTime < atom.xStartTime
ORDER BY Dupe.xStartTime DESC, Dupe.xAN) AS PriorStartTime,
[ElapsedSeconds]/60 AS ElapsedMinutes, DateDiff("s",[PriorStartTime],
[xStartTime]) AS ElapsedSeconds, [xStartTime]-[PriorStartTime] AS
ElapsedTime_01
FROM atom;

Which gives me this:

xAN xStartTime xText PriorStartTime ElapsedMinutes ElapsedSeconds
ElapsedTime_01
1 9/22/2007 3:29:15 AM test
2 9/22/2007 3:29:17 AM test1 9/22/2007 3:29:15 AM 0.03 2
2.31481535593048E-05
3 9/22/2007 3:29:20 AM test2 9/22/2007 3:29:17 AM 0.05 3
3.47222230629995E-05
4 9/22/2007 3:29:22 AM test3 9/22/2007 3:29:20 AM 0.03 2
2.31481462833472E-05
5 9/22/2007 3:29:26 AM test4 9/22/2007 3:29:22 AM 0.07 4
4.62962925666943E-05
6 9/22/2007 3:29:29 AM test5 9/22/2007 3:29:26 AM 0.05 3
3.47222230629995E-05
9 9/22/2007 9:10:57 AM test6 9/22/2007 3:29:29 AM 341.47 20488
0.237129629633273
10 9/22/2007 9:11:57 AM test7 9/22/2007 9:10:57 AM 1.00 60
6.94444439432118E-04
11 9/22/2007 10:11:57 AM test8 9/22/2007 9:11:57 AM 60.00 3600
4.16666666715173E-02

 

[Allen Browne]

Yes, you can use a parameter in the subquery.

As always, it's a good idea to declare the paramter (Parameter on Query
menu, in query design) if it is anything other than a Text type field.

Declare the parameter in the main query, and use it in the subquery.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Use a subquery to get the nearest prior time

If subqueries are new, here's an example:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

These were all good answers. I tried Allen Browne's suggestion using
a subquery. Is it possible to do this by parameters comparing two
rows like test1 and test4 within a subquery. I'd like to avoid
creating a function or another query. This is what I have so far:

SELECT atom.xAN, atom.xStartTime, atom.xText, (SELECT TOP 1
Dupe.xStartTime
FROM atom AS Dupe
WHERE Dupe.xStartTime < atom.xStartTime
ORDER BY Dupe.xStartTime DESC, Dupe.xAN) AS PriorStartTime,
[ElapsedSeconds]/60 AS ElapsedMinutes, DateDiff("s",[PriorStartTime],
[xStartTime]) AS ElapsedSeconds, [xStartTime]-[PriorStartTime] AS
ElapsedTime_01
FROM atom;

Which gives me this:

xAN xStartTime xText PriorStartTime ElapsedMinutes ElapsedSeconds
ElapsedTime_01
1 9/22/2007 3:29:15 AM test
2 9/22/2007 3:29:17 AM test1 9/22/2007 3:29:15 AM 0.03 2
2.31481535593048E-05
3 9/22/2007 3:29:20 AM test2 9/22/2007 3:29:17 AM 0.05 3
3.47222230629995E-05
4 9/22/2007 3:29:22 AM test3 9/22/2007 3:29:20 AM 0.03 2
2.31481462833472E-05
5 9/22/2007 3:29:26 AM test4 9/22/2007 3:29:22 AM 0.07 4
4.62962925666943E-05
6 9/22/2007 3:29:29 AM test5 9/22/2007 3:29:26 AM 0.05 3
3.47222230629995E-05
9 9/22/2007 9:10:57 AM test6 9/22/2007 3:29:29 AM 341.47 20488
0.237129629633273
10 9/22/2007 9:11:57 AM test7 9/22/2007 9:10:57 AM 1.00 60
6.94444439432118E-04
11 9/22/2007 10:11:57 AM test8 9/22/2007 9:11:57 AM 60.00 3600
4.16666666715173E-02