Calculate Total Hours by identifying Equal Time Intervals

Asked

Viewed 7,856 times

8

after a bit of a break in the head, I ask for help from you to assist in the following situation:

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

The point is that one trigger can occur while the other is still open, and disrupt at the time of the person’s total sum.

Ex:

  • Drive 1: 06/03 12:00 ----a-- 06/03 19:00
  • Drive 2: 06/03 13:00 ----a--- 06/03 15:00
  • Drive 3: 06/03 20:00 ----a--- 06/03 22:00

The correct total time, in this case would be 9 hours, because the second call does not count for being inside the first; but if I do it by the normal SQL subtraction method, it will result wrong.

Has anyone ever done anything like that? Calculating the longest final date - lowest starting date would also not work, because we would be counting the intervals between the drives together (in the example the interval between 1 and 3).

The base generates precisely the date and time separately, along with the number (1st, 2nd, 3rd...) of the drive. At most there are 7 drives.

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 show the total time worked by that plate: 8:30:00

sql sql-server logic sql-server-2005
  • 1

    Could you please edit your question by placing some SQL and the structure of the tables used for the query?

  • Okay, I hope I helped.... @.@

  • If, for example, we have two drives on the same day, one starting 9:00 and ending 14:00, and the other starting 11:00 and ending 15:30, as would the start time, the end time and the number of hours?

  • Acionamento 01 &#xA;data_inicio: 10/03/2015 9:00:00 &#xA;data_final: 10/03/2015 14:00:00 &#xA;quantidade_he: 05:00 &#xA; &#xA; &#xA;acionamento 02: &#xA;data_inicio: 10/03/2015 11:00:00 &#xA;data_final: 10/03/2015 15:30:00 &#xA;quantidade_he: 04:30 &#xA; --------> SUM OF DRIVING HOURS: 06:30 <---------

  • Yes, but from what I understand, the two drives can’t appear, right? It would be a record with only the shortest time and the longest time. Or would it be something else?

  • The goal is only to calculate the sum of driving hours of each MATRICULA_COLABORADOR, no matter if it does not show the amount of drives it has had; If the total driving hours is correct, all ok.

  • One question, are you modeling this table, or is it already in production (running on some system)? Why not use DateTime? I don’t understand Sqlserver, but I suppose that besides a simpler query, you would still have a much better performance (correct me if I’m wrong).

Show 2 more comments

4 answers

4

The thing already starts strange by date and time being in separate columns and be of the varchar type(? )(I’m guessing yes).

I grouped according to what I understood, which was to take the smallest hour of a drive and the largest date of a drive and calculate the amount of time in them. If there’s anything different from that, please explain further.

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 (I read in the comments that Voce wanted the somatic by matricula), 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.

  • http://sqlfiddle.com/#! 6/5f5ff/1/0 - Unfortunately FORMAT does not work on SQL-SERVER 2005. I fiddle to simulate and you understand. I want you to show the full time worked by the employee, 08:30:00.

  • The format we leave last. Now that I understand better, even the sql I passed you above would not be right for your case rs. Tell me one thing, in your first 9-hour example, if there was a quarter turn from 9:30 to 10:30, it would only count another half hour ?

  • Exactly. Because it already had an opening at 20:00 with a smaller closing than the 4th drive opening :)

2

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

The sentence went 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;

It’s returning the number of hours as decimal, but I believe it should be enough to answer.

  • So it’s really a start, thank you - but the main issue is that it doesn’t solve the issue of multiple drives.

  • You want all the drives to appear?

  • No. I don’t need it, I just need the right total time.

  • What would be different? What was expected and what the sentence generated? Can you please supplement your question with this information?

  • Hi, I edited the question and put a Sqlfiddle, see if you can understand better. THANK YOU!

  • @DHEKU I made a query based on your Fiddle. See if it is ok.

Show 1 more comment

2


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

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

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

that 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 that second code I used a secondary table only because I was too lazy to keep converting the date.

example you can find here

1

I would like to propose a solution:

I would do with an auxiliary table, totaling, something like that:

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

When opening the first drive I fill the value in the start time field, and I increment the drive column, at each drive new increment the drive totalizer, if there is any asset, if there is no create a new record.

When closing a drive, lowering the drive column, if it reaches zero, I closed all open and I can finish this record.

When opening a drive with no record opened here, I add a new.

I make the total for this table.

I find a more viable way, the data verification in this table can be done by Trigger in the other.

Browser other questions tagged sql sql-server logic sql-server-2005

You are not signed in. Login or sign up in order to post.