Working time calculation taking into account shifts
Hello everyone,
I am relatively new to Knime and I am faced with what I consider to be a very difficult task.
We have a machine park that reports interim statuses to us for each order number. There can therefore be any number of interim statuses (journal entries) for an order number.
These entries consist of the order number, a time stamp and the location of the machine.
I have now been given the task of determining the time differences between the respective entries. To make it more difficult, the difference should be determined taking into account the shift times and excluding Sundays and public holidays.
The shift times and public holidays can vary from location to location.
I therefore have three tables available as initial data:
Machine feedback
Public holidays
Shift times
In my previous workflow I have got as far as determining Sundays and public holidays, but I am not sure whether my approach is the right one. Since the machine’s data set only consists of the order number and time stamp, I had to transform these and move the time stamp of the previous entry down by one in order to be able to create a difference later. I then create a time series for each order number from the start and end dates. The time series allows me to easily check the holidays for each individual day.
I’m currently at a loss when it comes to calculating the time differences, taking into account the holidays and service times. There can be several possibilities for the time differences.
*
Time differences within one day
*
Time differences that extend over several days, and then come across Sundays and holidays + different service times
I would appreciate any help in solving my problem and am making my initial workflow including data available.
As a result, the time in minutes until the next journal entry was issued should be output for each order number line.
| Column 1 | Column 2 | Column 3 | Column 4 |
| A001 | 2024-08-14T12:53 | A | |
| A001 | 2024-08-16T16:00 | A | Timedifference |
| A001 | 2024-08-19T15:00 | A | Timedifference |
| A002 | 2024-11-11T12:53 | A | Timedifference |
| A003 | 2024-05-17T12:53 | B | Timedifference |
| A003 | 2024-05-21T16:00 | B | Timedifference |
| A003 | 2024-05-27T15:00 | B | Timedifference |
| A004 | 2024-11-11T12:53 | B | Timedifference |Thank you again for your help.