Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community ,
i have a new challenge in my script, and i can't figure out the solution.
I have a big table with "work hours" . It's something like that:
ID_WORKER DATE ENTRY_TIMESTAMP EXIT_TIMESTAMP sum( WORKHOURS)
000001 2016/08/15 08.00 12.30 4.5
The last field (WORKHOURS)is obtained in the script with:
WORKHOURS = (EXIT_TIMESTAMP - ENTRY_TIMESTAMP ) *60*24
My goal is to obtain the following Time Slot Analysis :
How can i reach my goal?
Thanks all
Perhaps something like this
LOAD
ID_WORKER,
DATE,
ENTRY_TIMESTAMP,
EXIT_TIMESTAMP,
'FROM ' & Time(ENTRY_TIMESTAMP + (IterNo()-1)/24,'hh.mm') &
' to ' & Time(ENTRY_TIMESTAMP + (IterNo())/24,'hh.mm') AS TimeSlots
If( ENTRY_TIMESTAMP + (IterNo())/24 <= EXIT_TIMESTAMP, 1,
EXIT_TIMESTAMP - ENTRY_TIMESTAMP + (IterNo()-1)/24 * 60 * 24) AS WORKHOURS
FROM
...source data...
WHILE
ENTRY_TIMESTAMP + (IterNo()-1)/24 <= EXIT_TIMESTAMP
;
Perhaps something like this
LOAD
ID_WORKER,
DATE,
ENTRY_TIMESTAMP,
EXIT_TIMESTAMP,
'FROM ' & Time(ENTRY_TIMESTAMP + (IterNo()-1)/24,'hh.mm') &
' to ' & Time(ENTRY_TIMESTAMP + (IterNo())/24,'hh.mm') AS TimeSlots
If( ENTRY_TIMESTAMP + (IterNo())/24 <= EXIT_TIMESTAMP, 1,
EXIT_TIMESTAMP - ENTRY_TIMESTAMP + (IterNo()-1)/24 * 60 * 24) AS WORKHOURS
FROM
...source data...
WHILE
ENTRY_TIMESTAMP + (IterNo()-1)/24 <= EXIT_TIMESTAMP
;
Thanks Gysbert.