Calculating SQL hours

Question

Calculating SQL hours

Navigation

#1 by (1 votes)

3

I need to implement a working hours report within a period.

My difficulty is this: the records for each day are on different lines and because of this, I'm having a hard time knocking in and out of the right days and I still have to subtract lunch time from that total.

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

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 arrived close, but I could not 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 the period of days and hours worked that day, more or less that.

Does anyone have any ideas? Would it be easier to deal with the program in any way? My initial idea is to get filtered by date period and collaborator.

mysql sql sql-select

asked by anonymous 14.09.2018 / 23:22

1 answer

Scope configuration file saving directory with User C # Protect php files from direct access without blocking the system

score 1 · Answer 1

I think a better approach is to only log entries and exits, for several reasons:

Some do not go out for lunch.
Some may end up having to go out 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 such cases:

Due to some problem, there was no entry record even though there was an exit record or vice versa.
There are two entries consecutively without one output in the middle or two outputs consecutively without an input in the middle.
Output record without matching entry.

My solution should work even for the case of the worker entering a day and leaving the next day, turning midnight at work. I am living proof that this case happens, I have had problems with HR hours for that very reason. The time in this case is counted the day you 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');

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

Once this is done, we only 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);

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 the seconds, the previous two the minutes and the rest are the hours.