Group records into non-contiguous hour-long bins

Question

I have event data of the form, where eventId is a long and time is Date/Time (shown below as just the time for simplicity, since all times will be on the same day):

eventId    time
---------------
   1       0712
   2       0715
   3       0801
   4       0817
   5       0916
   6       1214
   7       2255

I need to form groups containing an hour's worth of events, where the hour is measured from the first time in that group. Using the above data my groupings would be

Group 1 (0712 to 0811 inclusive): events 1, 2, 3
Group 2 (0817 to 0916 inclusive): events 4, 5
Group 3 (1214 to 1313 inclusive): event 6
Group 4 (2255 to 2354 inclusive): event 7

I've found plenty of examples of grouping data on predefined periods (e.g. every hour, day, 5 minutes) but nothing like what I'm trying to do. I suspect that it's not possible using straight SQL...it seems to be a chicken and egg problem where I need to put data into bins based on the data itself.

The cornerstone of this problem is coming up with the start time of each range but I can't come up with anything beyond the trivial case: the first start time.

The only way I can come up with is to do this programmatically (in VBA), where I SELECT the data into a temporary table and remove rows as I put them into bins. In other words, find the earliest time then grab that and all records within 1 hour of that, removing them from the table. Get the earliest time of the remaining records and repeat until the temp table is empty.

Ideally I'd like a SQL solution but I'm unable to come up with anything close on my own.

Answer 1

Some notes on a possible approach.

Dim rs As DAO.Recordset
Dim db As Database
Dim rsBins As DAO.Recordset
Dim qdf As QueryDef 'Demo

Set db = CurrentDb

'For demonstration, if the code is to be run frequently
'just empty the bins table
db.Execute "DROP TABLE Bins"
db.Execute "CREATE TABLE Bins (ID Counter, Bin1 Datetime, Bin2 Datetime)"

'Get min start times
db.Execute "INSERT INTO bins ( Bin1, Bin2 ) " _
   & "SELECT Min([time]) AS Bin1, DateAdd('n',59,Min([time])) AS Bin2 " _
   & "FROM events"

Set rsBins = db.OpenRecordset("Bins")

Do While True
    Set rs = db.OpenRecordset("SELECT Min([time]) AS Bin1, " _
    & "DateAdd('n',59,Min([time])) AS Bin2 FROM events " _
    & "WHERE [time] > (SELECT Max(Bin2) FROM Bins)")

    If IsNull(rs!Bin1) Then
        Exit Do
    Else
        rsBins.AddNew
        rsBins!Bin1 = rs!Bin1
        rsBins!bin2 = rs!bin2
        rsBins.Update
    End If
Loop

''Demonstration of final query.
''This will only run once and then error becaue the query exists, but that is
''all you need after that, just open the now existing binned query

sSQL = "SELECT events.Time, bins.ID, bins.Bin1, bins.Bin2 " _
     & "FROM events, bins " _
     & "GROUP BY events.Time, bins.ID, bins.Bin1, bins.Bin2 " _
     & "HAVING (((events.Time) Between [bin1] And [bin2]));"

Set qdf = db.CreateQueryDef("Binned", sSQL)
DoCmd.OpenQuery qdf.Name