Hello,
i have a following script written in the data load editor. I have a "worktime" column. Its an auto.calendar timestamp. As I want to analyse how much been done in the three shifts....plus the to be announced orders in the future "Planned", I set up following load script in addtion to the table:
if( Hour(worktime)>=6 and Hour(worktime)<=14,'Earlyshift',
if( Hour(worktime)>14 and Hour(worktime)<22,'Lateshift',
if( Hour(worktime)>=22 and Hour(worktime)<06,'Nightshift',if(isnull(worktime),'Planned', 'Nigtshift')))) as [Shift]
I have two problems now:
1.For example each week starts with the night shift on sunday at 22:00 and ends with the lateshift on Friday at 22:00. The problem I have now is that when an order start at Sunday before 24:00 the duration counts as sunday work and not to the night shift which also last until Monday to 6:00 in the morning.
2. when the process is starting in the early shift but last long into the second shift, all duration work time will account when it has begun. Instead of partially to the shift when it happened
Here is how the table should looke like:
| Ordernumber | Duration of Order in h | Shift | worktime | worktime finished |
| 1234 | 5 | Early | 6:30 | 23:00 |
| 5678 | 2 | Night | 22:00 | 0:00 |
| 5678 | 2 | Night | 00:00 | 02:00 |
| 3214 | 2 | Early | 12:00 | 14:00 |
| 3214 | 2 | Late | 14:00 | 16:00 |
So when everything happens in the same shift I should have only one row.
if it happens between two shifts I should get more then one row.
Does someone already encounter same problem or have a tip for me how to solve this?
Thank you very much.
Best.
