mysql – Calculation of SQL hours

Question:

I need to implement a reporting of hours worked within a period.

My difficulty is the following: the records for each day are on different lines and because of that, I'm having difficulty matching the entry and exit of the right days and I still have to subtract lunch time from that total.

And there is still another problem: what if on the day the person only made the entrance and lunch? I would have to calculate this period worked.

My table looks like this:

+------------------+-------------------+
|     horario      |       acao        |
+------------------+-------------------+
| 2018/09/14 08:00 | Entrada           |
| 2018/09/14 12:00 | Almoço            |
| 2018/09/14 13:00 | Volta almoço      |
| 2018/09/14 17:00 | Fim de expediente |
| 2018/09/15 08:00 | Entrada           |
| 2018/09/15 12:00 | Almoço            |
| 2018/09/15 13:00 | Volta almoço      |
| 2018/09/15 17:00 | Fim de expediente |
+------------------+-------------------+

I made a select that came close, but I couldn't subtract the lunch period:

Select id_colaborador, timediff(Max (horario) ,min(horario)) from tabela group by day(horario) , id_colaborador

I wanted a report that came up with the period of days and hours worked that day, more or less.

Does anyone have any ideas? Would dealing with the program somehow be easier? My initial idea is to receive filtering by date period and employee.

Answer:

I think a better approach is to only record inputs and outputs, for several reasons:

  • Some don't go out to lunch.
  • Some may end up having to go out more than once in the middle of the day for whatever reason.
  • Some may go out for lunch and not come back for the day for some reason.

I will not consider these cases:

  • Due to some problem, there was no check-in although there was a check-out or vice versa.

  • Either two entries consecutively without an entry in the middle or two exits consecutively without an entry in the middle occur.

  • Checkout without corresponding checkout.

My solution should work even for the case of the worker who comes in one day and leaves the next day, turning midnight at work. I'm living proof that this case happens, I've had problems with schedules registered with HR for this exact reason. The time in this case is computed on the day you entered.

First, let's build a database for testing:

CREATE TABLE tabela (
    id_colaborador int NOT NULL,
    horario datetime NOT NULL,
    acao varchar(20)
) engine = InnoDB CHARSET = utf8mb4;

INSERT INTO tabela VALUES (1, '2018/09/14 08:00', 'Entrada');
INSERT INTO tabela VALUES (1, '2018/09/14 12:00', 'Almoço');
INSERT INTO tabela VALUES (1, '2018/09/14 13:00', 'Volta almoço');
INSERT INTO tabela VALUES (1, '2018/09/14 17:00', 'Fim de expediente');
INSERT INTO tabela VALUES (1, '2018/09/15 08:00', 'Entrada');
INSERT INTO tabela VALUES (1, '2018/09/15 12:00', 'Almoço');
INSERT INTO tabela VALUES (1, '2018/09/15 13:00', 'Volta almoço');
INSERT INTO tabela VALUES (1, '2018/09/15 17:00', 'Fim de expediente');
INSERT INTO tabela VALUES (2, '2018/09/14 08:30', 'Entrada');
INSERT INTO tabela VALUES (2, '2018/09/14 12:30', 'Almoço');
INSERT INTO tabela VALUES (2, '2018/09/14 13:30', 'Volta almoço');
INSERT INTO tabela VALUES (2, '2018/09/14 17:30', 'Fim de expediente');
INSERT INTO tabela VALUES (2, '2018/09/15 08:30', 'Entrada');
INSERT INTO tabela VALUES (2, '2018/09/15 12:30', 'Almoço');
INSERT INTO tabela VALUES (2, '2018/09/15 13:30', 'Volta almoço');
INSERT INTO tabela VALUES (2, '2018/09/15 17:30', 'Fim de expediente');

-- Virou a meia-noite no trabalho!
INSERT INTO tabela VALUES (3, '2018/09/14 17:00', 'Entrada');
INSERT INTO tabela VALUES (3, '2018/09/15 02:00', 'Almoço');

Now, let's select the input and output pairs:

SELECT
    entrada.id_colaborador,
    entrada.horario AS entrada,
    (
        SELECT MIN(s.horario)
        FROM tabela s
        WHERE s.acao IN ('Almoço', 'Fim de expediente')
        AND s.id_colaborador = entrada.id_colaborador
        AND s.horario > entrada.horario
    ) AS saida
FROM tabela entrada
WHERE entrada.acao IN ('Entrada', 'Volta almoço');

The output should be this:

+----------------+----------------------+----------------------+
| id_colaborador | entrada              | saida                |
+----------------+----------------------+----------------------+
| 1              | 2018-09-14T08:00:00Z | 2018-09-14T12:00:00Z |
| 1              | 2018-09-14T13:00:00Z | 2018-09-14T17:00:00Z |
| 1              | 2018-09-15T08:00:00Z | 2018-09-15T12:00:00Z |
| 1              | 2018-09-15T13:00:00Z | 2018-09-15T17:00:00Z |
| 2              | 2018-09-14T08:30:00Z | 2018-09-14T12:30:00Z |
| 2              | 2018-09-14T13:30:00Z | 2018-09-14T17:30:00Z |
| 2              | 2018-09-15T08:30:00Z | 2018-09-15T12:30:00Z |
| 2              | 2018-09-15T13:30:00Z | 2018-09-15T17:30:00Z |
| 3              | 2018-09-14T17:00:00Z | 2018-09-15T02:00:00Z |
+----------------+----------------------+----------------------+

That done, now we just have to join the input-output pairs of the day:

SELECT
    x.id_colaborador AS id_colaborador,
    DAY(x.entrada) AS dia,
    SUM(timediff(x.entrada, x.saida)) AS periodo
FROM (
    SELECT
        entrada.id_colaborador AS id_colaborador,
        entrada.horario AS entrada,
        (
            SELECT MIN(s.horario)
            FROM tabela s
            WHERE s.acao IN ('Almoço', 'Fim de expediente')
            AND s.id_colaborador = entrada.id_colaborador
            AND s.horario > entrada.horario
        ) AS saida
    FROM tabela entrada
    WHERE entrada.acao IN ('Entrada', 'Volta almoço')
) x
GROUP BY x.id_colaborador, DAY(x.entrada);

The output should be this:

+----------------+------------+---------+
| id_colaborador | dia        | periodo |
+----------------+------------+---------+
| 1              | 2018-09-14 | 80000   |
| 1              | 2018-09-15 | 80000   |
| 2              | 2018-09-14 | 80000   |
| 2              | 2018-09-15 | 80000   |
| 3              | 2018-09-14 | 90000   |
+----------------+------------+---------+

This value returned in the period is in the format HMMSS , that is, the last two digits are seconds, the previous two are minutes, and the remainder are hours.

Scroll to Top
AllEscort