Time Slot Analysis

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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

     ;

[#3]

Thanks Gysbert.