Muchas gracias a todos por sus opiniones y a la ayuda proporcionada, creo que ya termine la consulta, me quedo así
Código SQL:
Ver originalSELECT IdHora, SEC_TO_TIME( SUM( HT ) ) HorasTrabajadas
FROM (
SELECT IdHora, TIME_TO_SEC( SUBTIME( '06:00', HorIni ) ) HT
FROM HorasTrab
WHERE (
HorIni < '06:00'
AND HorFin > '06:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME( HorFin, '18:00' ) ) HT
FROM HorasTrab
WHERE (
HorIni < '18:00'
AND HorFin > '18:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( SUBTIME( '06:00', HorFin ) ) HT
FROM HorasTrab
WHERE (
HorIni >'06:00'
AND HorFin < '06:00'
AND HorIni < HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME( ADDTIME( SUBTIME( '24:00', HorIni ) , '06:00' ) , SUBTIME( HorFin, '18:00' ) ) ) HT
FROM HorasTrab
WHERE (
HorIni > '18:00'
AND HorFin > '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME( SUBTIME( '06:00', HorIni ) , ADDTIME( HorFin, '06:00' ) ) ) HT
FROM HorasTrab
WHERE (
HorIni < '06:00'
AND HorFin < '06:00'
AND HorIni > HorFin
)UNION SELECT IdHora, TIME_TO_SEC( ADDTIME('06:00',HorFin ) ) HT
FROM HorasTrab
WHERE (
HorIni > '06:00'
AND HorIni < '18:00'
AND HorFin <'06:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC( ADDTIME('06:00',SUBTIME( '24:00', HorIni ) ) ) HT
FROM HorasTrab
WHERE (
HorIni > '18:00'
AND HorFin >'06:00'
AND HorFin < '18:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC('12:00') HT
FROM HorasTrab
WHERE (
HorIni < '18:00'
AND HorIni > '06:00'
AND HorFin < '18:00'
AND HorFin >'06:00'
AND HorIni > HorFin
)
UNION SELECT IdHora, TIME_TO_SEC('00:00') HT
FROM HorasTrab
WHERE (
HorIni >= '06:00'
AND HorFin <= '18:00'
OR HorIni =HorFin
)
)T1
GROUP BY IdHora
Cualquier sugerencia es bienvenida