NJ Buses to Port Authority b/w and 8:15 w/ Lat/Lon

buses_to_pabt_8-815_lat_ln.sql

	SELECT
	  BusRoute.route_id
	  ,bustrip.trip_id
	  ,bustrip.trip_headsign
	  ,BusStopTime.arrival_time
	  ,busstopTime.stop_sequence
	  ,busstop.stop_name
	  ,busstop.stop_lat
	  ,busstop.stop_lon
	  ,busstop.stop_id
	  ,StopAddr.addr
	  --,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
	LEFT JOIN stop_addr StopAddr
      ON BusStop.stop_id = StopAddr.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('08:15:00','HH24:MI:SS')
	    AND
	      to_timestamp(arrival_time, 'HH24:MI:SS') > to_timestamp('08:00:00','HH24:MI:SS')
		AND stop_id = '3511' -- Port Authority
	    )
	ORDER BY
	  BusRoute.route_id
	  ,BusTrip.trip_id
	  ,BusStopTime.stop_sequence
	  ASC