Valid working hours

Question

Valid working hours

Navigation

#1 by (1 votes)

1

How do I calculate the time spent on calls taken by an employee? But always within the valid work schedule, and knowing that an employee's work schedule is between 08:00 and 18:00.

For example: table named_status_log.

___________________________________________________________
|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 |    
|_______|___________|___________|___________|_____________|

Because they can handle multiple calls per day, they have the possibility to leave the calls paused until the best moment to serve you, including leaving for another day.

The calculation of the time spent in answering the call I can recover, but I can not visualize the best way to leave the calculation of breaks during working hours. In this example mentioned above the calculated pause of this call should be 1: 30min, that is, it would only count towards the end of your working hours, and from your starting time of the day.

My query looks 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 it keeps counting the time that should theoretically be out of range.

How should my approach be in this case? Any suggestions?

mysql sql

asked by anonymous 26.07.2018 / 15:04

1 answer

Center svg within a rect element Hibernate Does Not Delete

score 1 · Answer 1

You can use the [IF()][1] function so that when the time is less than @HoraEntrada you change to @HoraEntrada itself and if it is greater than @HoraSaida also assume @HoraSaida , it 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')