Announcements
cancel
Showing results for
Did you mean:
Contributor III

## sum without overlapping intervals in load script

Ciao,

i would like to sum the intervals of machine work only when they are not overlapped.

this is because if the machine work on two production order i don't want to duplicate worked time.

i hope the following table would explain better than my english.

 ENG 1 interval 1 13/03/2018 10.13 11.15 1.02 ENG 1 interval 2 13/03/2018 11.3 12.3 1 ENG 1 interval 3 13/03/2018 12 12.3 0.3

 TOTAL 2.02

interval 3 is overlapped.

1 Solution

Accepted Solutions
Contributor III
Author

i've solved in this way:

temp:

if(begin<peek(end),peek(ID),rowno()) as ID

residen time_table;

in this way if the begin is overlapped to previous end it takes same id, then i used

time_differences:

max(end)-min(begin) as time_difference

resident temp group by ID;

5 Replies
MVP

So you want to calculate this sum in the script?

You could do it with the help of Peek() or Previous() ? maybe with a logic like this:

...

if(Key = previous(Key) and Date = previous(Date) and Start < previous(End), 1, 0) as Flag

...

You might need some more if-conditions to fetch all possible combinations but then using this flag as a set analysis condition in the UI you could sum the values. Whereby also a cummulating within the script will be possible.

- Marcus

Contributor III
Author

i would like to obtain a sum like the one represented in attechment: when the interval is overlapped i would have the difference between the max end hour and the min value

Contributor III
Author

i've solved in this way:

temp:

if(begin<peek(end),peek(ID),rowno()) as ID

residen time_table;

in this way if the begin is overlapped to previous end it takes same id, then i used

time_differences:

max(end)-min(begin) as time_difference

resident temp group by ID;

Partner - Contributor III

It's an old thread, but maybe someone will see it.

I think in temp table you need to add one more condition to cover the case where you have at least 3  simultaneous intervals like this:

 begin end ENG 1 interval 1 13/03/2018 10 11 1 ENG 1 interval 2 13/03/2018 10.1 10.3 0.2 ENG 1 interval 3 13/03/2018 10.6 12 1.4 TOTAL 2

Your condition: if(begin<peek(end),peek(ID), rowno()) as ID ... applied for interval 3 will not work ok (it will give false) and it will duplicate interval 3, although interval 1 is still running. It will compare 10.6 with 10.3, but it should compare 10.6 with 11.

This will be resolved if we also put the next condition in temp:

if( end < peek(end), peek(end), end) as end

That way it will replace end of interval 2 (10.3) with end of interval 1 (11) and it will not consider interval 3 having a new ID.

Community Browser