Hello,
I have this table:
JID | PKEY | DataChange_start | DataChange_end | minute_start | minute_end | WorkingTime |
2837562 | EOSMKP-11057 | 1/6/2016 10:53 | 1/20/2016 11:07 | 846834 | 852248 | 90:14:00 |
6477190 | EOSMKP-18851 | 8/17/2016 14:08 | 8/26/2016 16:18 | 933429 | 937339 | 65:10:00 |
6382303 | EOSMKP-17728 | 6/8/2016 16:51 | 6/16/2016 17:18 | 906592 | 909859 | 54:27:00 |
6413632 | EOSMKP-17938 | 6/17/2016 16:59 | 6/27/2016 12:31 | 910380 | 913352 | 49:32:00 |
6407718 | EOSMKP-17830 | 6/17/2016 16:55 | 6/27/2016 9:47 | 910376 | 913188 | 46:52:00 |
6220720 | EOSMKP-14798 | 3/22/2016 19:00 | 3/29/2016 18:12 | 876420 | 879120 | 45:00:00 |
and i need to display WorkingTime, on week level, something like this:
In this example i used round 90 Working hours.
I calculate minutes to find out working hours like this :
Let vStart = floor(40179);
Let vEnd = floor(now());
TempCalendar:
LOAD
DataChange,
working_hour,
IF(isnull(peek(working_hour)), 0, peek('minute') + working_hour) AS minute;
//
LOAD
text(timestamp(Data,'YYYY-MM-DD hh:mm')) as DataChange,
IF(match(floor(WeekDay(Data)), 5, 6), 0,
IF((hour(Data) >= 9 and hour(Data) < 18), 1, 0)
) AS working_hour;
LOAD
$(vStart) + (IterNo()-1)/24/60 As Data,
rowno()
AutoGenerate 1 While $(vStart) + (IterNo()-1)/24/60 <= $(vEnd);
left join (time2)
LOAD
DataChange AS DataChange_start,
minute as minute_start
RESIDENT TempCalendar;
left join (time2)
LOAD
DataChange AS DataChange_end,
minute as minute_end
RESIDENT TempCalendar;
Can anyone give me an idea on how can i solve this please ?
Thank you,