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
Could you please edit your question by placing some SQL and the structure of the tables used for the query?
– Leonel Sanches da Silva
Okay, I hope I helped.... @.@
– dHEKU
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?
– Leonel Sanches da Silva
Acionamento 01 
data_inicio: 10/03/2015 9:00:00 
data_final: 10/03/2015 14:00:00 
quantidade_he: 05:00 
 
 
acionamento 02: 
data_inicio: 10/03/2015 11:00:00 
data_final: 10/03/2015 15:30:00 
quantidade_he: 04:30 
 --------> SUM OF DRIVING HOURS: 06:30 <---------
– dHEKU
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?
– Leonel Sanches da Silva
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.
– dHEKU
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).– Guilherme Nascimento