Joining results of two different aggregate functions on the same table

Question

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.

The first query finds the last time an aircraft arrived at a certain point:

select aircraft_id, MAX(arrival_datetime) as last_arrival
from flight_schedule
where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
group by aircraft_id

and the second query finds the last time an aircraft has left this point:

select aircraft_id, MAX(departure_datetime) as last_departure
from flight_schedule
where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
group by aircraft_id

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.

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.

Answer 1

The easiest solution would be to

• turn each statement into a subquery
• join the results together

SQL Statement

select la.aircraft_id, la.last_arrival, ld.last_departure
from  (
    select aircraft_id, MAX(arrival_datetime) as last_arrival
    from flight_schedule
    where arrival_datetime < '2011-09-19 04:00:00.000' and arrival_point='ABCD'
    group by aircraft_id
  ) la
  full outer join (
    select aircraft_id, MAX(departure_datetime) as last_departure
    from flight_schedule
    where departure_datetime < '2011-09-19 04:00:00.000' and departure_point='ABCD'
    group by aircraft_id
  ) ld on ld.aircraft_id = la.aircraft_id

Note that I've used a full outer join. Most likely an inner join would suffice. The full outer join is only needed if there's ever an arival_datetime without a departure_datetime or vice versa (wich is unlikely to happen).