Início / dba / Perguntas / 264363
Accepted
giannis christofakis
Asked: 2020-04-06 09:55:54 +0800 CST

Contar 3 horas extras em uma determinada janela de data e hora

  • 772

Tenho que implementar um sistema de controle de acesso para uma empresa, que será capaz de reportar o acesso de cada funcionário em uma área específica do prédio para fornecer um relatório. Um dos requisitos é fornecer uma lista dos funcionários com mais de 3 horas extras em uma determinada janela de tempo.

O esquema da tabela que contém as informações necessárias é o seguinte. ACTIONinforma se o funcionário entrou ou saiu de um determinado nível do edifício. O resto é o tempo, o funcionário e o nível.

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

um conjunto de resultados de amostra

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

Acredito que sei como posso estimar as horas extras considerando a soma do tempo que cada funcionário passou dentro das dependências do prédio que dará o total de horas. Mas eu só posso imaginar como eu poderia fazer isso na camada de aplicação.

Existe uma maneira de estimar as horas extras puramente com SQL?

EDITAR

De acordo com os comentários, deve haver uma sequência estrita entre as ações de entrar-sair, portanto, criou-se o seguinte conjunto de dados .

mysql

1 respostas

  1. Best Answer

    Com base no comentário de @Akina, criei dois conjuntos um para as ações IN e outro para o OUT, que juntei a eles e subtraí o tempo gasto dentro das instalações. Portanto, posso calcular o total de horas de trabalho para cada funcionário em qualquer dia específico.

    Cheguei à seguinte consulta.

    SET @row_number = 0;
SET @row_number1 = 0;

select SEC_TO_TIME( SUM( TIME_TO_SEC(abb.overtimes) ) ) overtimes, employee_id
from
    (select TIMEDIFF(ab.working_time,'08:00:00') overtimes, ab.employee_id from
        (select SEC_TO_TIME( SUM(aaaa.timespent) ) working_time, accessDate, aaaa.employee_id
            from
            (select aaa.OUTtime as OUTtime, aaa.INtime, TIME_TO_SEC( TIMEDIFF(OUTtime, INtime) ) as timespent, DATE(OUTtime) as accessDate, aaa.level_id, aaa.employee_id
             from
                 (select aa1.TIMESTAMP as OUTtime, aa2.TIMESTAMP as INtime, aa1.level_id, aa1.employee_id
                  from (select
                            (@row_number:=@row_number + 1) row_num,
                            a1.TIMESTAMP, a1.employee_id, a1.level_id
                        from access_action a1
                        where a1.ACTION = 'OUT'
                        order by employee_id asc, TIMESTAMP desc) aa1
                           INNER JOIN
                       (select
                            (@row_number1:=@row_number1 + 1) row_num,
                            a2.TIMESTAMP, a2.employee_id, a2.level_id
                        from access_action a2
                        where a2.ACTION = 'IN'
                        order by employee_id asc, TIMESTAMP desc) aa2
                       ON aa1.row_num = aa2.row_num) aaa) aaaa
        group by accessDate, aaaa.employee_id
        having accessDate >= '2017-05-26' and accessDate <= '2017-05-30'
        AND working_time > '08:00:00') ab) abb
group by employee_id
having overtimes > '03:00:00';

    Realizando a consulta nos dados o resultado é o seguinte.

    overtimes   employee_id
03:50:00    4
    • 0

relate perguntas