Calculate Total Hours, identifying equal time intervals

You can create a procedure and in this procedure calculate the time. I have never worked with sql server, so I do not know how to create this procedure and most likely my query is not the most appropriate, but you can have ideas how to improve it.

But the sql I used to get the 30600 seconds equivalent to 8.5 hours was that.

declare @temp table (id INTEGER, data_ini datetime, data_fim datetime);

insert into @temp
select ID_ENTRADA,
convert(datetime, DATA_INICIO_HE + ' ' +HORA_INICIO_HE, 103),
convert(datetime, DATA_TERMINO_HE + ' ' +HORA_TERMINO_HE, 103)
from tabela;

declare @curid int;
declare @maxid int;
declare @data_ini datetime;
declare @data_fim datetime;

select @curid = min(id), @maxid = max(id) from @temp;

while @curid <= @maxid
begin
    select 
    @data_ini = data_ini, 
    @data_fim = data_fim 
    from @temp where id = @curid;

    update @temp set data_ini = (select min(data_ini) FROM @temp where data_fim between @data_ini and @data_fim) where id = @curid;
    delete from @temp where data_fim between @data_ini and @data_fim and id > @curid;

    update @temp set data_fim = (select max(data_fim) FROM @temp where data_ini between @data_ini and @data_fim) where id = @curid;
    delete from @temp where data_ini between @data_ini and @data_fim and id > @curid;

    delete from @temp where 
    data_ini between @data_ini and @data_fim and 
    data_fim between @data_ini and @data_fim and
    id <> @curid;

    select @curid = min(id) from @temp where id > @curid;
end

select SUM(DATEDIFF(SECOND, data_ini, data_fim)) from @temp

the example you can find here

This code also works

declare @temp table (id INTEGER, data_ini datetime, data_fim datetime);

insert into @temp
select ID_ENTRADA,
convert(datetime, DATA_INICIO_HE + ' ' +HORA_INICIO_HE, 103),
convert(datetime, DATA_TERMINO_HE + ' ' +HORA_TERMINO_HE, 103)
from tabela;

select sum(DATEDIFF(SECOND, m, m2))  from (
  select distinct
  (select min(t2.data_ini) from @temp as t2 where t.data_ini between t2.data_ini and t2.data_fim or t.data_fim between t2.data_ini and t2.data_fim) as m,
  (select max(t2.data_fim) from @temp as t2 where t.data_ini between t2.data_ini and t2.data_fim or t.data_fim between t2.data_ini and t2.data_fim) as m2
  from @temp as t
) a

In this second code I used a child table only because I was too lazy to always convert the date.

example you can find here

The thing already starts strange by date and time being in separate columns and being of type varchar (?) (I'm assuming yes).

I grouped it according to what I understood, which was to take the shortest time of a drive and the largest date of a drive and calculate the amount of time in them. If it's any different then please explain further.

• Functional example in SQL Fiddle

As it does not 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 (li in the comments you wanted to add by adding), still using 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.

For this answer, I am using this SQLFiddle, provided by the question author .

The sentence looks like this:

SELECT MATRICULA_COLABORADOR, DATA_AUTORIZACAO, DATA_INICIO_HE, 
    CONVERT(VARCHAR(10), MIN(T.HORA_INICIO), 108) AS HORA_INICIO_HE, 
    DATA_TERMINO_HE, CONVERT(VARCHAR(10), MAX(T.HORA_TERMINO), 108) AS HORA_TERMINO,
  (DATEDIFF(second, MIN(T.HORA_INICIO), MAX(T.HORA_TERMINO)) * 1.0) / 3600 AS QUANTIDADE_HE 
  FROM (
    SELECT MATRICULA_COLABORADOR, DATA_AUTORIZACAO, DATA_INICIO_HE, 
    CONVERT(datetime, DATA_INICIO_HE + ' ' + HORA_INICIO_HE, 103) AS HORA_INICIO, 
    DATA_TERMINO_HE, CONVERT(datetime, DATA_TERMINO_HE + ' ' + HORA_TERMINO_HE, 103) AS HORA_TERMINO
FROM TABELA) T
GROUP BY T.MATRICULA_COLABORADOR, T.DATA_AUTORIZACAO, T.DATA_INICIO_HE, T.DATA_TERMINO_HE;

You're returning the number of hours as a decimal, but I think that should be enough for the answer.

I would like to propose a solution:

I would do with an auxiliary table, totalizing, something like this:

MATRICULA | HORA_INICIO          | HORA_FIM | ACIONAMENTO
XXX       | INICIO_ACIONAMENTO 1 |  NULL    | 1

When you open the first drive, fill in the value in the start time field, and increment the triggering column, with each new drive increment the drive totalizer, if there is an asset, if it does not exist create a new registry.

When I close a trigger, I decrease the triggering column, if it reaches zero, I closed all open and I can finish this record.

When I open a drive with no registry open here, I add a new one.

I make the total for this table.

I find a more viable path, checking the data in this table can be done by triggering the other.