?
Solved

find time gaps between records

Posted on 2014-09-10
9
Medium Priority
?
188 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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 48

Expert Comment

by:Dale Fye
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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 48

Accepted Solution

by:
Dale Fye earned 1600 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
 
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 48

Expert Comment

by:Dale Fye
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

764 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