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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what you want is what in Oracle and SQL Server is the "LAG" or "LEAD" functions, which return the value of the previous/next row
in ms access you don't have that function, so you must do a subquery to get that value... a domain function would also do, but is not recommended for performance reasons

select t.ActualQueryTime
  ,  ( select min(n.ActualQueryTime) from yourtable n where n.ActualQueryTime > t.ActualQueryTime ) nextTime  
  from yourtable t

and you can of course do maths on the 2 values.
hope this helps
0
Dale FyeCommented:
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.
0
FordraidersAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Access will generally let you use a computed column within the same query, but I don't think that will work here because of the group by.

You might try:

SELECT Temp.ActualQueryTime, Temp.NextTime,
, DateDiff("n", ActualQueryTime, NextTime) as Gap
FROM (
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
) as Temp
WHERE DateDiff("n", Temp.ActualQueryTime, Temp.NextTime) > 2
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks very much to both !!
0
FordraidersAuthor Commented:
Worked great Dale,
0
FordraidersAuthor Commented:
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.
??
0
Dale FyeCommented:
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.
0
FordraidersAuthor Commented:
very good Thanks for the additional help !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.