How to determine if there's a gap in time field

[tubbers]

I have a date/time field in a table and I need to be able to find out if there is a gap between any of the records grouped by the Code field.

Sample data:

ID Code Time
1 1234 5/12/04 4:32:32
2 1234 5/12/04 4:34:19
3 1234 5/12/04 4:59:00
4 5678 5/12/04 1:00:00
5 5678 5/12/04 1:29:00
6 5678 5/12/04 1:59:59
7 9012 5/12/04 2:00:00
8 3456 5/12/04 2:00:00
9 3456 5/12/04 2:29:00

What I need to be able to show is that there is a gap of more than 30 minutes between record 5 and 6.

What is the best way to go about doing this in a query?

 

[PHV]

A starting point:
SELECT A.ID, A.Code, A.Time, Format(A.Time-Nz(Max(B.Time),A.Time),"h:nn:ss") AS Gap
FROM tblCodeTime AS A LEFT JOIN tblCodeTime AS B
ON A.Code=B.Code AND A.Time>B.Time
GROUP BY A.ID, A.Code, A.Time
HAVING 24*60*(A.Time-Nz(Max(B.Time),A.Time))>30;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tubbers]

I tried working with what you suggested but it seems to lock up and not finish. Watching the status bar, it moves quickly but once all the little bars are filled and you think that the query results are going to appear, it stops and just stays there. I will try running it overnight to see if it ever finishes or if it just hangs up.

 

[JonFer]

This slightly modified version of PHV's query will run faster but give all records more than 30 minutes beyond the starting record. It also does not show a record if there isn't another record >30 minutes after it.

SELECT A.ID, A.Code, A.Time, 24*60*(A.Time-B.Time) AS Gap
FROM tblCodeTime AS A INNER JOIN tblCodeTime AS B
ON A.Code=B.Code AND A.Time>B.Time
WHERE 24*60*(A.Time-B.Time)>30

Try the above to see what you get. Then you can modify it by adding Min() or Max() or both:

SELECT A.ID, A.Code, A.Time, Min(24*60*(A.Time-B.Time)) AS MinGap
FROM tblCodeTime AS A INNER JOIN tblCodeTime AS B
ON A.Code=B.Code AND A.Time>B.Time
WHERE 24*60*(A.Time-B.Time)>30
Group By A.ID, A.Code, A.Time


John

http://www.interautomateconsulting.com

 

[tubbers]

Thanks John.

While your version worked much faster, it didn't put the results in a format that would be understandable to the users.

I ended up using PHV's version but I have it running from a form so that one of the other variables (ctlCode) passes the code as criteria to the query. While not the best solution, it only takes a few minutes to run the query now.

Thanks for your help.