asked on

find time gaps between records

access 2010

I have a query result that i need to now create another query that will show me gaps between records.

I need to know if i can find the time between each each record.
What is the gap between record 2 and 1
What is the gap between record 3 and 2
etc....
???
is this possible..

maybe a pivot table ?

ActualQueryTime
9/10/2014 8:31:02 AM
9/10/2014 8:44:44 AM
9/10/2014 8:46:32 AM
9/10/2014 8:46:38 AM
9/10/2014 8:46:43 AM
9/10/2014 8:46:48 AM
9/10/2014 8:47:38 AM
9/10/2014 8:47:45 AM
9/10/2014 8:47:48 AM
9/10/2014 8:47:54 AM
9/10/2014 8:47:58 AM
9/10/2014 8:48:02 AM
9/10/2014 8:48:02 AM
9/10/2014 8:48:34 AM

Thanks
fordraiders

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

Or, instead of the sub query, you could do:

SELECT T1.ActualQueryTime
, Min(T2.ActualQueryTime) as NextTime
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.ActualQueryTime < T2.ActualQueryTime
GROUP BY T1.ActualQueryTime

Depending on the size of your recordset, I think this will run faster than the subquery method.

Fordraiders

ASKER

dale,
Would there be anyway to combine these 2 "queries now"...so i have a 3 column result set ?
SELECT T1.ActualQueryTime
, Min(T2.ActualQueryTime) as NextTime
FROM Search_Terms_Hourly as T1
LEFT JOIN Search_Terms_Hourly as T2
ON T1.ActualQueryTime < T2.ActualQueryTime
GROUP BY T1.ActualQueryTime

and then i did this:

SELECT DateDiff("n", ActualQueryTime, NextTime) AS MinutesDiff
FROM Search_Terms_Hourly_Setup1

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Thanks very much to both !!

Fordraiders

ASKER

Worked great Dale,

Fordraiders

ASKER

Dale, I want to understand something, In the where clause.
does the
WHERE DateDiff("n", Temp.ActualQueryTime, Temp.NextTime) > 2

>2 stand for 2min ?

So its possible to lower to 1 or just leave out the where clause all together.
??

Dale Fye

Yes, 2 minutes.

As a operations analyst, I might change the DateDiff to count seconds instead of minutes.

Then you could run a query against that query to determines the average gap between records and the standard deviation, and then only select those records that fall outside some appropriate range. Maybe that would be >65 or >70.

Fordraiders

ASKER

very good Thanks for the additional help !