title: Determining pairs of arrival and departures to calculate time spent at destination
body: <p>I'm trying to write a SQL query that will let me know the time spent at a particular airport based on arrival and departure times.</p>
  
  <pre><code>CREATE TABLE Flights
      (`ACID` varchar(6), `TIME` datetime, `STATUS` varchar(1))
  ;
  
  INSERT INTO Flights
      (`ACID`, `TIME`, `STATUS`)
  VALUES
      ('XXXX', '2014-01-01 18:09:00', 'A'),
      ('XXXX', '2014-01-02 15:25:00', 'D'),
      ('XXXX', '2014-01-06 19:53:00', 'A'),
      ('XXXX', '2014-01-07 14:01:00', 'D'),
      ('XXXX', '2014-01-12 16:06:00', 'A'),
      ('XXXX', '2014-01-12 17:00:00', 'D'),
      ('YYYY', '2014-01-13 18:10:00', 'A'),
      ('YYYY', '2014-01-13 18:56:00', 'D'),
      ('XXXX', '2014-01-16 20:40:00', 'A'),
      ('XXXX', '2014-01-17 14:49:00', 'D'),
      ('ZZZZ', '2014-01-17 21:42:00', 'A'),
      ('ZZZZ', '2014-01-17 22:17:00', 'D'),
      ('XXXX', '2014-01-21 00:30:00', 'A'),
      ('XXXX', '2014-01-23 15:35:00', 'D')
  ;
  </code></pre>
  
  <p>The status row is either 'A' for arrival or 'D' for departure.</p>
  
  <p>The output should aggregate the time spent (which is the difference between departure and arrival times) by the ACID field. </p>
  
  <p>There may be cases when there is an arrival record, but no matching departure record.</p>
  
  <p>I'm having a tough time "determining the pairs" so that I can do the date difference, etc..</p>
  
  <p>RDBMS is MSSQL 2012</p>