Calculation of SQL hours

Question

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

My difficulty is as follows: the records of each day are in different lines and because of this, I’m having difficulty to beat the entry and exit of the right days and still have to subtract the lunch time from this total.

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

My table looks something 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 would come the period of days and hours worked that day, more or less that.

Does anyone have any ideas? To treat the program in any way would be easier? My initial idea is to get filter by date period and collaborator.

Answer 1

I think a better approach is to record only entries and exits for various reasons:

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

I will not consider these cases:

• Due to some problem, there was no entry record although there was an exit record or vice versa.

• two entries occur consecutively without one exit in the middle or two exits consecutively without one entry in the middle.

• Output record without corresponding input record.

My solution should work even for the worker who enters one day and leaves the next, turning midnight at work. I am living proof that this case happens, I’ve had problems with schedules registered in HR for this very reason. The time in this case is calculated on the day it entered.

First, let’s build a test database:

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');

That should be the way out:

+----------------+----------------------+----------------------+
| 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 |
+----------------+----------------------+----------------------+

Done that, now we just have to join the entry-exit 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);

That should be the way out:

+----------------+------------+---------+
| 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 period is in format HMMSS, that is, the last two digits are the seconds, the previous two minutes and the remaining are the hours.