Buses to Port Authority

buses_to_pabt.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 = '1' 
and
bustrip.trip_id IN (
SELECT
 StopTime.trip_id
FROM stops StopName

LEFT JOIN stop_times StopTime
  ON StopName.stop_id = StopTime.stop_id
WHERE
  StopName.stop_name like '%AUTHORITY%')
AND
BusStopTime.arrival_time ~ '^05.*'
ORDER BY
  BusRoute.route_id
  ,BusTrip.trip_id
  ,BusStopTime.stop_sequence
  ASC