Valid working hours

Question

How do I calculate the time spent on calls answered by an employee? However always within valid working hours, and knowing that the working hours of an employee is between 08hs and 18hs.

For example: table called.

___________________________________________________________
|cod_log|cod_chamado|inicio_log |fim_log    | status_log  |
| 001   | 100       |25/07 16:00|25/07 17:00| Atendimento |
| 002   | 100       |25/07 17:00|26/07 08:30| Pausado     |
| 003   | 100       |26/07 08:30|           | Atendimento |    
|_______|___________|___________|___________|_____________| 

As they can answer multiple calls a day, they have the possibility to leave the calls in pause until the best time to serve it, including leaving for another day.

The calculation of the time spent on answering the call I can recover, but I’m not seeing the best way to leave the calculation of breaks within working hours. In this example above cited the calculated pause of this call should be 1:30 min, that is to say it would only count until the end of your working hours, and from your work start time.

My query is like this:

SET @HoraEntrada  = '08:00:00';
SET @HoraSaida    = '20:00:00';
SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, q.isl_data_ini, q.isl_data_fim))
as `difference` 
FROM insuporte_status_log q 
WHERE hour(q.isl_data_ini) >= @HoraEntrada and hour(q.isl_data_fim) <= @HoraSaida
and  q.isl_sch_codigo = '100405-573'
and (q.isl_status = 'Pausado')

And yet you keep counting the time that theoretically should be out of range.

What should my approach be like in this case? Any suggestions?

Answer 1

You can use the function [IF()][1] so that when the time is less than @HoraEntrada you switch to own @HoraEntrada and if it’s bigger than @HoraSaida also assume the @HoraSaida, would look something like this:

SET @HoraEntrada  = '08:00:00';
SET @HoraSaida    = '20:00:00';
SELECT SEC_TO_TIME(
    TIMESTAMPDIFF(
        SECOND,
        IF(q.isl_data_ini < HoraEntrada,
            @HoraEntrada,
            IF(q.isl_data_ini > @HoraSaida,
                @HoraSaida,
                q.isl_data_ini
            )
        ),
        IF(q.isl_data_ini < HoraEntrada,
            @HoraEntrada,
            IF(q.isl_data_fim > @HoraSaida,
                @HoraSaida,
                q.isl_data_fim
            )
        )
    )
) as `difference` 
FROM insuporte_status_log q 
WHERE q.isl_sch_codigo = '100405-573'
and (q.isl_status = 'Pausado')