GROUPING BY Every Minute Of A Day

[George Moore]

This has stumped me as I can't come up with any GROUP BY logic which fits the bill

I work for a Train Company which runs circa 1,300 services per day and I need to count how may trains are running on a minute by minute basis from 00:00 until 23:59. A highly simplified example :-

--------------------------
|Train.. | DEP.. | ARR.. |
|--------|-------|-------|
|Train A | 10:00 | 12:30 |
|Train B | 06:30 | 14:40 |
|Train C | 11:15 | 15:00 |
--------------------------

06:30 - 09:59 = 1 train running each minute (B)
10:00 - 11:14 = 2 trains running each minute (A + B)
11:15 - 12:30 = 3 trains running each minute (A + B + C)
12:31 - 14:40 = 2 trains running each minute (B + C)
14:41 - 15:00 = 1 train running each minute (C)

Any suggestions gratefully received as my only alternative appears to be nesting some SQL within a VBA loop which means running a total of 1,440 queries

George :banghead:

 

[MarkK]

This is not really a GROUP BY problem, it's sequential. So think of adding a train when it departs, and subtracting it when it arrives. So, query your schedule for data like . . .

TrainID | EventType | DateTime

. . . where the EventType is +1 for departure, and -1 for arrival. Then open that as a recordset sorted by the DateTime of the arrival and departure, as the case may be.
Then loop thru that recordset, and sum your event types as you go, and that will be the count of trains currently running, because one is added for a departure, and subtracted for an arrival. If you did this in a report, just keep a running sum of the EventType, and that will be your count of trains currently running for the given time.

Makes sense?

 

[MarkK]

The query will be a UNION query on your schedule table, like . . .

SELECT TrainID, 1 As EventType, Departure As DateTime FROM Schedule WHERE IsDeparture
UNION
SELECT TrainID, -1, Arrival As DateTime FROM Schedule WHERE IsArrival

Then sort that data . . .

SELECT t.* FROM (
   SELECT TrainID, 1 As EventType, Departure As DateTime FROM Schedule WHERE IsDeparture
   UNION
   SELECT TrainID, -1, Arrival As DateTime FROM Schedule WHERE IsArrival
   ) As t 
ORDER BY t.DateTime

See what's going on there?

 

[CJ_London]

would be simpler if you had an extra table with each minute (i.e. 1440 records) containing a datetime field populated with each minute

then your query would be something like

SELECT format(tblMinutes.minute,"hh:nn") as Minute, count(*) as NoOfTrains
FROM tblMinutes INNER JOIN tblTrains ON tblminutes.minute>=tblTrains.dep and tblminutes.minute<=tblTrains.arr

you'll also need something to account for trains running over midnight

Doesn't get you to your required final output but you should be able to group it by start/end times for each count

 

[George Moore]

Thanks MarkK & CJ_London for your speedy posts

I suspected that there was going to be no alternative to using VB to loop through a recordset in some shape or form however it was worth asking the question just in case....

CJ - Trains which run over midnight get picked up via an IIF clause as the arrival time is less than the departure

Many thanks to you both for taking the time to respond. It is 20 years since I wrote my first SQL string and nothing has stumped me like this

Regards

George