Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

find time gaps between records

Posted on 2014-09-10
9
Medium Priority
?
192 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 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 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

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 49

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

581 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