title: Joining results of two different aggregate functions on the same table
body: <p>I'm trying to combine two queries in order to determine which aircraft will be at a particular point (point ABCD) just before a particular time (2011-09-19 04:00:00.000). So far, I generate the last time an aircraft arrived at this point, and the last time an aircraft departed the point. My current idea is that if the last time it arrived is greater then the last time it departed the aircraft is still at point ABCD just before the specified time.</p>
  
  <p>The first query finds the last time an aircraft arrived at a certain point:</p>
  
  <pre><code>select aircraft_id, MAX(arrival_datetime) as last_arrival
  from flight_schedule
  where arrival_datetime &lt; '2011-09-19 04:00:00.000' and arrival_point='ABCD'
  group by aircraft_id
  </code></pre>
  
  <p>and the second query finds the last time an aircraft has left this point:</p>
  
  <pre><code>select aircraft_id, MAX(departure_datetime) as last_departure
  from flight_schedule
  where departure_datetime &lt; '2011-09-19 04:00:00.000' and departure_point='ABCD'
  group by aircraft_id
  </code></pre>
  
  <p>Both of these queries generate the appropriate responses. I realise that in order to compare the last_departure to the last_arrival fields I need to join the tables somehow.</p>
  
  <p>I'm not an SQL whiz, and in the past any table joins I've done have been between two completely different tables and haven't involved any aggregate functions so my normal subqueries and structure hasn't worked.</p>