Solved

find time gaps between records

Posted on 2014-09-10
9
178 Views
Last Modified: 2014-09-11
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
0
Comment
Question by:fordraiders
  • 5
  • 3
9 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 40316314
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40316587
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40317088
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 40317127
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40317172
Thanks very much to both !!
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317175
Worked great Dale,
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317229
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40317347
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
 
LVL 3

Author Comment

by:fordraiders
ID: 40317551
very good Thanks for the additional help !
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now