Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am looking to calculate total working hours for a process which includes multiple tasks ( StartTime, EndTime) . Tricky part is that these tasks may go parallel also which means we will be having overlapping hours which we need to exclude.
For e.g
Task | Start Time(DD-MM-YYYY hh:mm:ss) | End Time(DD-MM-YYYY hh:mm:ss) |
Task1 | 18-05-2021 8:00 am | 18-05-2021 12:00 pm |
Task2 | 18-05-2021 8:00 am | 18-05-2021 10:00 am |
Task3 | 18-05-2021 12:30 pm | 18-05-2021 1:00 pm |
Task4 | 18-05-2021 2:00 pm | 18-05-2021 3:00 pm |
Here total working hours are 5 hours 30 minutes
( 8-12 = 4 hours;
12:30-1:00 = 30 minutes ;
2-3=1 hour)
Note: We have to avoid all those hours which are overlapping e.g Task2 --> (8-10 ) as these hours were already part of (8-12) in task 1
Need a logic to calculate this total working hours
Hi @ahmed_qlik,
This can be a little bit tricky, but you can get it by joining the periods table with itself by looking at which periods overlap (start time in one period between start and end times in the other). I have created an example of how to get that overlapped periods:
Times:
Load
Task,
Timestamp#(StartTime, 'DD-MM-YYYY hh:mm tt') AS StartTime,
Timestamp#(EndTime, 'DD-MM-YYYY hh:mm tt') AS EndTime
Inline [
Task StartTime EndTime
Task1 18-05-2021 8:00 am 18-05-2021 12:00 pm
Task2 18-05-2021 8:00 am 18-05-2021 10:00 am
Task3 18-05-2021 12:30 pm 18-05-2021 1:00 pm
Task4 18-05-2021 2:00 pm 18-05-2021 3:00 pm
] (delimiter is '\t');
Pass1:
Load Distinct
StartTime AS Start1,
EndTime AS End1
Resident Times;
Outer Join
Load Distinct
StartTime AS Start2,
EndTime AS End2
Resident Times;
Drop Table Times;
Pass2:
Load
Start1 AS Start,
Max(End1, End2) AS End
Resident Pass1
Where
(Start2 >= Start1 And Start2 <= End1 And End2 > End1) // Period 2 starts between Period 1 but ends later
Or
(Start1 = Start2 And End1 = End2) // Same Period, so gets not overlapped periods
Group By Start1
;
Drop Table Pass1;
Periods:
Load Distinct
'Period ' & RowNo() AS Period,
Timestamp(Start) AS Start,
Timestamp(End) AS End,
Timestamp(End - Start) AS Duration
Resident Pass2
Order by Start;
Drop Table Pass2;
Tag Fields Start, End, Duration With $timestamp;
The result is:
I think it could be possible to add another pass for some casuistic not presented in the sample data, but it will be similar to Pass2.
JG
Thanks Juan , really appreciate your quick support.
However if there is a condition where only some part of the duration is overlapped like
Task1, 18-05-2021 8:00 am, 18-05-2021 12:00 pm
Task7, 18-05-2021 10:00 am, 18-05-2021 12:30 pm
if you notice, Task 7 is overlapped with Task1 but not completely ( 30 minutes ).
What we should do in that condition?
Regards
Ahmed
I have modified it little bit ,
Pass1:
Load Distinct
//Port,
StartTime AS Start1,
EndTime AS End1
Resident Times;
Outer Join
Load Distinct
Port,
StartTime AS Start2,
EndTime AS End2
Resident Times;
Drop Table Times;
Pass2:
Load distinct
Port,
Start1 AS Start,
Max(End1, End2) AS End
Resident Pass1
Where
(Start2 >= Start1 And Start2 <= End1 And End2 > End1) // Period 2 starts between Period 1 but ends later
Or
(Start1 = Start2 And End1 = End2) // Same Period, so gets not overlapped periods
Group By
Port,
Start1 order by
Port,
Start1 asc
;
Drop Table Pass1;
//exit SCRIPT;
Pass3:
load *,
if (Port=Previous(Port) ,if( Start > Previous(Start) and End >=Previous(End) and Start<Previous(End), Start-Previous(End)),0) as New_duration
Resident Pass2 ;
drop Table Pass2;
//exit SCRIPT;
Periods:
Load Distinct
'Period ' & RowNo() AS Period,
Port,
Timestamp(Start) AS Start,
Timestamp(End) AS End,
Timestamp(End - Start) AS Duration,
New_duration
Resident Pass3
Order by Port,Start;
Drop Table Pass3;
//then adding duration and New_duration ; it seems working now but if there is a whole day difference between Start and end time, then it is not working.
Regards
Ahmed