all_buses_morning_commute

all_buses_morning_commute.sql

SELECT
  BusRoute.route_id
  ,bustrip.trip_id
  ,bustrip.trip_headsign
  ,BusStopTime.arrival_time
  ,busstopTime.stop_sequence
  ,busstop.stop_name
  --,BusTripShape.shape_pt_sequence
FROM routes BusRoute
LEFT JOIN trips BusTrip
  ON BusRoute.route_id = BusTrip.route_id
LEFT JOIN stop_times BusStopTime
  ON BusTrip.trip_id = BusStopTime.trip_id
LEFT JOIN stops BusStop
  ON BusStopTime.stop_id = BusStop.stop_id
WHERE
BusTrip.direction_id = '0' --this is outbound to NY
AND BusTrip.service_id = '9' --this is the weekday service
and
bustrip.trip_id IN (
	SELECT trip_id FROM stop_times
	WHERE
	  to_timestamp(arrival_time, 'HH24:MI:SS') < to_timestamp('09:00:00','HH24:MI:SS') 
    AND
      to_timestamp(arrival_time, 'HH24:MI:SS') > to_timestamp('06:00:00','HH24:MI:SS')
	AND stop_id = '3511')
ORDER BY
  BusRoute.route_id
  ,BusTrip.trip_id
  ,BusStopTime.stop_sequence
  ASC