title: Subquery with values from same row
body: <p>I have a small problem with MySQL
  I have database (Arr_Times) with following columns
  Mysql_id
  Name
  ARR_DT (Arrival datetime)
  DC_DT (Departure datetime)</p>
  
  <pre><code>Mysql_id | Name           | ARR_DT              | DC_DT
  1        |Doe, Jane       | 2011-08-01 00:02:00 | 2011-08-01 13:32:00
  2        |Doe, John       | 2011-08-01 00:23:00 | 2011-08-01 18:32:00
  3        |Smith, Peter    | 2011-08-01 00:12:00 | 2011-08-01 08:38:00
  4        |Unknown, Jane   | 2011-08-01 13:03:00 | 2011-08-01 14:02:00
  5        |Day, Doris      | 2011-08-01 10:10:00 | 2011-08-01 13:32:00
  6        |Bergman, Ingrid | 2011-08-01 11:35:00 | 2011-08-01 13:59:00
  7        |Jones, Tom      | 2011-08-01 00:12:00 | 2011-08-01 22:49:00
  8        |Schubert, Mark  | 2011-08-01 18:45:00 | 2011-09-01 02:22:00
  9        |Doe, Jane       | 2011-08-01 23:04:00 | 2011-010-01 03:32:00
  </code></pre>
  
  <p>Generally there is approx. 150-220 rows for each day and departure time can be up to 48 hours later from arrival time.
  I’m trying to find out how many persons are in department in any given time</p>
  
  <p>For example automatically calculating persons in department for each row when person arrives with rule: count everyone whom ARR_DT  &lt;= current_row’s ARR_DT AND DC_DT > current_row’s ARR_DT
  To have a result, for example to be exported to excel pivot table.</p>
  
  <pre><code>mysql_id | Name | ARR_DT_unix | DC_DT_unix | Number_In_department
  </code></pre>
  
  <p>But this goes through whole database for each row and is highly CPU consuming. Is there any better solution for this issue.</p>
  
  <p>Following is what I am currently attempting, but I am having incorrect results</p>
  
  <pre><code>SELECT
    T1.mysql_id,
    T1.Name, 
    FROM_UNIXTIME(T1.ARR_DT_unix) AS ARR_DT,
    FROM_UNIXTIME(T1.DC_DT_unix) AS DC_DT,
    (SELECT count(T2.mysql_id) FROM dates T2 WHERE T1.ARR_DT_unix &lt;= T2.ARR_DT_unix AND T1.DC_DT_unix &gt; T2.ARR_DT_unix ) AS Person_Count 
  FROM 
    dates T1
  INNER JOIN 
   dates T2
  ON T1.mysql_id = T2.mysql_id
  ORDER BY T1.ARR_DT_unix;
  </code></pre>
  
  <p>What would be the best option for this problem?</p>
  
  <p>Thanks,
  Marko</p>