How to calculate difference between 2 time columns in distinct tables?

I need to calculate the difference between EntryHourSchedule (the ideal time) and EntryHourRegistered (the real time) of a worker’s assitance, but both are in 2 different tables: ScheduleDetail (* to 1 with Schedule) and Registry.

I used

datediff(second, start_date, end_date) / 3600.0

for calculate this with the next tables.

Just like this:

SELECT I.IdTrabajador, I.Fecha
    , Datediff(second
        , I.HoraInicioRefrigerio
        , (SELECT HD.HoraInicioRefrigerio
            FROM [rrhh].TrabajadorHorarioDetalle HD INNER JOIN [rrhh].TrabajadorHorario H
                ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
            WHERE H.Estado = 1 AND HD.Estado = 1 AND HD.Dia = (SELECT Datename(dw, I.Fecha))
            )
        ) / 3600.0 as Cantidad
    , 0.00 as Costo
FROM [rrhh].TrabajadorIncidencia I

But result in an Error: The subquery has returned more than one value, which is not correct when it goes after =,! =, <, <=,>,> = Or when it is used as an expression.

Finally, it should be shown, for example: IdTrabajador: 1 | Fecha: '2019-01-29' | Cantidad: 1.5

Thanks.

Answer

You missed the link on IdTrabajador in your subquery:

SELECT I.IdTrabajador, I.Fecha
    , Datediff(second
        , I.HoraInicioRefrigerio
        , (SELECT HD.HoraInicioRefrigerio
            FROM [rrhh].TrabajadorHorarioDetalle HD INNER JOIN [rrhh].TrabajadorHorario H
                ON HD.IdTrabajadorHorario = H.IdTrabajadorHorario
            WHERE H.Estado = 1 AND HD.Estado = 1 AND HD.IdTrabajador = I.IdTrabajador AND HD.Dia = (SELECT Datename(dw, I.Fecha))
            )
        ) / 3600.0 as Cantidad
    , 0.00 as Costo
FROM [rrhh].TrabajadorIncidencia I