weekday_113

gistfile1.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'
AND BusTrip.service_id = '9' --this is the weekday service
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 StopTime.trip_id IN (
    	SELECT StopTime.trip_id
        FROM stops StopName
        LEFT JOIN stop_times StopTime
          ON StopName.stop_id = StopTime.stop_id
        WHERE StopTime.arrival_time < '09:00'
        )
    )
AND
--BusTrip.trip_headsign ~ '^113.*'
--AND BusStopTime.arrival_time ~ '^05.*'


ORDER BY
  BusRoute.route_id
  ,BusTrip.trip_id
  ,BusStopTime.stop_sequence
  ASC