Subquery with values from same row

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)

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

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

For example automatically calculating persons in department for each row when person arrives with rule: count everyone whom ARR_DT <= 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.

mysql_id | Name | ARR_DT_unix | DC_DT_unix | Number_In_department

But this goes through whole database for each row and is highly CPU consuming. Is there any better solution for this issue.

Following is what I am currently attempting, but I am having incorrect results

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 <开发者_运维知识库= T2.ARR_DT_unix AND T1.DC_DT_unix > 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;

What would be the best option for this problem?

Thanks, Marko

You can use

 T1.mysql_id,

in where clause to restrict search in your sub query this might solve your problem