sql – Calculate Total Hours by Identifying Equal Time Intervals

Question:

After breaking my head a little, I ask for your help to help in the following situation:

I have a problem in SQL (Sql Server 2005) calculating hours; Basically I have to calculate the sum of hours worked in technical triggers.

The point is that one trigger can occur while the other is still open, and interfere with the person's total sum.

Ex:

  • Activation 1: 06/03 12:00 —to— 06/03 19:00
  • Activation 2: 03/06/13:00 —a— 03/06/15:00
  • Activation 3: 06/03 20:00 —a— 06/03 22:00

The correct time total in this case would be 9 hours, as the second trigger does not count for being within the first; But if I do it using the normal SQL subtraction method, it will turn out wrong.

Has anyone ever done anything like that? Calculating the largest end date – smallest start date would not work either, as we would be counting the intervals between triggers together (in the example the interval between 1 and 3).

The base exactly generates the separate date and time, together with the number (1st, 2nd, 3rd…) of the activation. There are a maximum of 7 activations.

Thanks in advance!

I made the example table to make it easier:

CREATE TABLE tabela (
    ID_ENTRADA INTEGER, 
    MATRICULA_COLABORADOR INTEGER, 
    DATA_AUTORIZACAO VARCHAR(50),
    DATA_INICIO_HE VARCHAR(50),
    HORA_INICIO_HE VARCHAR(50), 
    DATA_TERMINO_HE VARCHAR(50), 
    HORA_TERMINO_HE VARCHAR(50), 
    QUANTIDADE_HE VARCHAR(50), 
    ACIONAMENTO INTEGER
);

INSERT INTO tabela values (1, 100, '09/03/2015', '14/03/2015', '14:00:00', '14/03/2015', '16:00:00', '02:00:00', 1);
INSERT INTO tabela values (2, 100, '09/03/2015', '14/03/2015', '15:30:00', '14/03/2015', '17:00:00', '01:30:00', 2);
INSERT INTO tabela values (3, 100, '09/03/2015', '14/03/2015', '19:00:00', '14/03/2015', '22:00:00', '02:00:00', 3);
INSERT INTO tabela values (4, 100, '09/03/2015', '15/03/2015', '08:00:00', '15/03/2015', '10:00:00', '02:00:00', 1);
INSERT INTO tabela values (5, 100, '09/03/2015', '15/03/2015', '08:30:00', '15/03/2015', '10:30:00', '02:00:00', 2);

Example in SqlFiddle

In short: I want you to present the total hours worked by this enrollment: 8:30:00

Answer:

The thing starts out weird because date and time are in separate columns and are of type varchar(?) (I'm guessing so).

I grouped it as I understood, which was to take the smallest time of a trigger and the largest date of a trigger and calculate the amount of time in them. If it's anything different, please explain further.

As it doesn't fit in the comments, my suggestion:

select MATRICULA_COLABORADOR, ACIONAMENTO, 
  convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103) as data_hora_inicio_minimo, 
  convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103) as data_hora_termino_maximo, 
  concat(
    format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) / 60 / 60, '00'), ':', 
    format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) / 60 % 60, '00') , ':',
     format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) % 60, '00')
  ) as tempo
from tabela 
group by MATRICULA_COLABORADOR, ACIONAMENTO;

edit (I read in the comments that you wanted the sum per registration), still using the sql above:

select MATRICULA_COLABORADOR, SUM(segundos) as total_segundos, 
concat(
    format(SUM(segundos) / 60 / 60, '00'), ':', 
    format(SUM(segundos) / 60 % 60, '00') , ':',
    format(SUM(segundos) % 60, '00')
  ) as total_tempo   
from (select MATRICULA_COLABORADOR, ACIONAMENTO, 
  convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103) as data_hora_inicio_minimo, 
  convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103) as data_hora_termino_maximo, 
  concat(
    format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) / 60 / 60, '00'), ':', 
    format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) / 60 % 60, '00') , ':',
     format(datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) % 60, '00')
  ) as tempo, datediff(
      second, 
      convert(datetime, MIN(DATA_INICIO_HE) + ' ' + MIN(HORA_INICIO_HE), 103), 
      convert(datetime, MAX(DATA_TERMINO_HE) + ' ' + MAX(HORA_TERMINO_HE), 103)
    ) segundos
from tabela 
group by MATRICULA_COLABORADOR, ACIONAMENTO) as temp group by MATRICULA_COLABORADOR;

If so, let me know so I can improve the final sql.

Post Views: 3
Scroll to Top