Calculate total time duration for different tasks with exclusion of overlapping hours

[ahmed_qlik]

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

@rwunderlich

 @sunny_talwar

@marcus_sommer

@rubenmarin 

@wassenaar

@Michael_Tarallo 

@christof schwarz

[JuanGerardo]

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

[ahmed_qlik]

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

[ahmed_qlik]

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