Quizas esta es mejor.... analizala....
(de la misma fuente que la anterior)
cambiando maquinas por empleados es que el ejemplo es lo que estas buscando!!!!
Código pre:
Ver originalGaps in a time series
Advanced time series analysis generally requires custom software, but
straightforward SQL queries can answer simple time series questions.
You have a jobtimes table with columns ID, job, machine, start_time, and
stop_time. You wish to know which machines have had gaps between activity
periods. It's a version of "Find available booking periods":
drop table jobtimes;
create table jobtimes(id int, machine smallint, start_time timestamp, stop_time timestamp);
insert into jobtimes values(1,1,'2011-7-1 08:00:00', '2011-7-1 10:00:00');
insert into jobtimes values(2,1,'2011-7-1 11:00:00', '2011-7-1 14:00:00');
insert into jobtimes values(3,2,'2011-7-1 08:00:00', '2011-7-1 09:00:00');
insert into jobtimes values(4,2,'2011-7-1 09:00:00', '2011-7-1 10:00:00');
insert into jobtimes values(5,3,'2011-7-1 08:00:00', '2011-7-1 08:30:00');
insert into jobtimes values(6,3,'2011-7-1 10:00:00', '2011-7-1 12:00:00');
select * from jobtimes;
+------+---------+---------------------+---------------------+
| id | machine | start_time | stop_time |
+------+---------+---------------------+---------------------+
| 1 | 1 | 2011-07-01 08:00:00 | 2011-07-01 10:00:00 |
| 2 | 1 | 2011-07-01 11:00:00 | 2011-07-01 14:00:00 |
| 3 | 2 | 2011-07-01 08:00:00 | 2011-07-01 09:00:00 |
| 4 | 2 | 2011-07-01 09:00:00 | 2011-07-01 10:00:00 |
| 5 | 3 | 2011-07-01 08:00:00 | 2011-07-01 08:30:00 |
| 6 | 3 | 2011-07-01 10:00:00 | 2011-07-01 12:00:00 |
+------+---------+---------------------+---------------------+
SELECT
a.machine,
a.stop_time AS 'Unused From',
Min(b.start_time) AS 'To'
FROM jobtimes AS a
JOIN jobtimes AS b ON a.machine=b.machine AND a.stop_time < b.start_time
GROUP BY a.stop_time
HAVING a.stop_time < MIN(b.start_time)
ORDER BY machine;
+---------+---------------------+---------------------+
| machine | Unused From | To |
+---------+---------------------+---------------------+
| 1 | 2011-07-01 10:00:00 | 2011-07-01 11:00:00 |
| 3 | 2011-07-01 08:30:00 | 2011-07-01 10:00:00 |
+---------+---------------------+---------------------+