Task/Ticket Duration

[Anonymous]

I am facing the challenge to calculate the duration of Tasks/tickets Duration and exclude times outside working hours and exclude breaks.

Currently working hours and breaks are defined in a table (to make it more flexible). In script I run through all data (using peek) and calculate the duration by subtracting breaks. Unfortunatley this is very slow (e.g. 20000 rows need about 3 minutes).

Is there a Chance to calculate this on surface?

e.g. Working hour 8:00 - 17:00

       Break1: 9:00-9:15

       Break2: 12:00-12:30

       Break3: 14:00-14.45

Taskevent might be

08:30-13:00    - which should result in 3h 45 Duration

09:10-09:30   - which should result in 15 minutes

10:00-10:10  - which should result in 10 minutes

Task can occur at any time and be ended at any time

I prefer a solution with is able to handle flexible times (working hours, breaks, no of breaks etc.) so I can use it for other Projects as well

Any idea? Is

[Gysbert_Wassenaar]

Is there a Chance to calculate this on surface?

Do you mean in chart expressions? That will make the user experience the performance penalty. And performance will likely be even worse than calculating it in the script.

Perhaps it's an idea to implement incremental loading if you haven't already. Then you'll only have to process the new records and not all of them again and again.

[marcus_sommer]

About 3 minutes for 20000 rows seems to be quite long even with peek() functions - could you share the script?

- Marcus

[Anonymous]

i can share the script later on

meanwhile I think about an optimization

right now I get each datarow (using peek) and

then in an inner Loop I go through all breaks (using peek)

maybe I can get rid of the inner loop?

I will provide the script part this evening

[Anonymous]

thats what I was afraid of, that is not faster on surface.

I need to check if incremental load is possible, rigth now i get full load and data only for 3 months
(as this is a new Project). But size will increase in future. So incremental load should really be considered!

[Gysbert_Wassenaar]

I'm pretty sure you can get rid of the inner loop. Perhaps this discussion helps: Calculate hours between two Date/Time strings

[swuehl]

Looping over each record using something like FOR ... NEXT will indeed take ages.

You can use WHILE clauses in your load, like this:

Set DecimalSep ='.';

Let vWorkStart = Num('08:00');

Let vWorkEnd = Num('17:00');

Let vBreak1Start = Num('09:00');

Let vBreak1End =  Num('09:15');

Let vBreak2Start = Num('12:00');

Let vBreak2End =  Num('12:30');

Let vBreak3Start = Num('14:00');

Let vBreak3End =  Num('14:45');

TASKS:

LOAD Recno() as TaskID, * INLINE [

Start, End

08:30,13:00   

09:10,09:30  

10:00,10:10

07:00, 19:00 

];

TMP:

LOAD *, interval(End-Start ) as Duration;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End

  WHILE iterno() <=2;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End

  WHILE iterno() <=2;

LOAD TaskID, pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start, pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End

  WHILE iterno() <=2;

LOAD TaskID, RangeMax($(vWorkStart), Start) as Start, RangeMin($(vWorkEnd), End) as End RESIDENT TASKS;

DROP TABLE TASKS;

RESULT:

LOAD TaskID, Interval(Sum(Duration)) as DurationTOTAL

Resident TMP

GROUP BY TaskID;

[swuehl]

Noticed that the while loops generated unnecessary amount of records, here is a slightly improved version:

Set DecimalSep ='.';


Let vWorkStart = Num('08:00');
Let vWorkEnd = Num('17:00');
Let vBreak1Start = Num('09:00');
Let vBreak1End =  Num('09:15');
Let vBreak2Start = Num('12:00');
Let vBreak2End =  Num('12:30');
Let vBreak3Start = Num('14:00');
Let vBreak3End =  Num('14:45');


TASKS:
LOAD Recno() as TaskID, * INLINE [
Start, End
08:30,13:00    
09:10,09:30   
10:00,10:10
07:00, 19:00  
];

TMP:
LOAD *, interval(End-Start ) as Duration;


LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End
WHILE iterno() <= (1 -  (End > $(vBreak2End))); 

LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End
WHILE iterno() <= (1  -( End > $(vBreak1End))); 


LOAD TaskID, 
  pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start, 
  pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End
WHILE iterno() <=2; 


LOAD TaskID, 
  RangeMax($(vWorkStart), Start) as Start, 
  RangeMin($(vWorkEnd), End) as End 
RESIDENT TASKS; 


DROP TABLE TASKS;


RESULT:
LOAD TaskID, Interval(Sum(Duration)) as DurationTOTAL
Resident TMP
GROUP BY TaskID;

[Anonymous]

wow, great

I will try your suggestion asap

that's why I like this community. I get fast answers and good advices

I will give feedback after tests

thanks a lot

[swuehl · Accepted solution]

I think you can even limit the first WHILE clause, too.

TMP:

LOAD *, interval(End-Start ) as Duration;

LOAD TaskID,

  pick(iterno(), RangeMin(Start,$(vBreak3Start)), RangeMax($(vBreak3End),Start)) as Start,

  pick(iterno(), RangeMin(End,$(vBreak3Start)), RangeMax(End,$(vBreak3End))) as End

WHILE iterno() <= (1 -  (End > $(vBreak2End)));

LOAD TaskID,

  pick(iterno(), RangeMin(Start,$(vBreak2Start)), RangeMax($(vBreak2End),Start)) as Start,

  pick(iterno(), RangeMin(End,$(vBreak2Start)), RangeMax(End,$(vBreak2End))) as End

WHILE iterno() <= (1  -( End > $(vBreak1End)));

LOAD TaskID,

  pick(iterno(), RangeMin(Start,$(vBreak1Start)), RangeMax($(vBreak1End),Start)) as Start,

  pick(iterno(), RangeMin(End,$(vBreak1Start)), RangeMax(End,$(vBreak1End))) as End

WHILE iterno() <= (1 -( End > $(vBreak1Start)));

LOAD TaskID,

  RangeMax($(vWorkStart), Start) as Start,

  RangeMin($(vWorkEnd), End) as End

RESIDENT TASKS;