Query records with time difference

[Quazy]

Hello
I am creating a little application, using VB.net, and Access database.
I want to count the number of records whitch have more than 6 hours difference between records.

Is it possible to have a query to count the number of recordset where the "Time End" and "Time Start" of different records are more than 6 hours, by a "SELECT Count(ID) As Expr1 from Table Where"... ?

So far I have only managed to achieve this by loading the table to a datagridview, and loop through this to count the records with more than 6 hours time difference between the last recordset "Time End", and next recordset "Time Start".

My table look like this:

ID Time Start Time End Operation
1 26.03.2015 05:00 26.03.2015 12:00 Loading
2 26.03.2015 17:00 26.03.2015 20:00 Loading
3 28.03.2015 06:00 28.03.2015 17:00 Loading
4 28.03.2015 20:00 27.03.2015 02:00 Loading
5 04.04.2015 07:00 04.04.2015 15:00 Loading

(I like query to count 3 records here)
I don't know how to create this query, or if it is possible.

If not possible, I may add a column to the table, insert time difference from last recordset, but this only works if each recordset are inserted/updated in chronologic order.

Thanks

[djjeavons]

Hi

The following query uses the DateDiff function in Access to determine if TimeEnd is 6 hours or greater than TimeStart:

SELECT Schedule.ID, Schedule.TimeStart, Schedule.TimeEnd, Schedule.Operation
FROM Schedule
WHERE (((DateDiff("h",[TimeStart],[TimeEnd]))>=6));

HTH

[Quazy]

Hi, I need to check the 6 hours time difference between each records.
like this:

ID 1 TimeEnd and ID 2 TimeStart if >=6 hours
ID 2 TimeEnd and ID 3 TimeStart if >=6 hours
ID 3 TimeEnd and ID 4 TimeStart if >=6 hours
and so on....

and then count only the records witch have more than 6 hours difference.

[DaveAmour]

I think to do this you need to do a cartesian join with the table on itself and implement the where condition.

[Quazy]

Proberbly loading the access table to a datatable, and loop through this with a counter, with "datediff" is the best way. At least this works.

For i = Table.Rows.Count - 1 To 1 Step -1
If DateDiff(DateInterval.Hour, CDate(Table.Rows(i)(6)), CDate  (Table.Rows(i - 1)(5))) >= 6 Then
counter = counter + 1
End If
Next