I have been torturing myself with this problem. I haven't been able to find anything similar online. I have been trying to make something of it in MS Excel but nothing worth mentioning. It is not strictly speaking a programming question, maybe it is more of a mathematical problem. I would be thankful for any input or suggestion. I would say the problem is similar to this one, only equal effort is not an assumption in my case.
The problem is: In my case, each job has a start time, end time, and output. There is also an additional parameter: goal output for a shift (or 480 minutes). The table would look something like this (for example):
| Order | Start | End | Output | Goal output(480min) |
|---|---|---|---|---|
| 1 | 6:00:00 | 14:00:00 | 200 | 400 |
| 2 | 6:15:00 | 13:00:00 | 500 | 3000 |
| 3 | 7:15:00 | 11:30:00 | 100 | 1000 |
| 4 | 9:00:00 | 12:25:00 | 10 | 500 |
My goal here would be to calculate how much time in each interval is spent on each process. So, for example - orders 2 & 3 are overlapping from 7:15:00 to 11:30:00. That is 4 hours and 15 minutes. How much of that time is spent on process 2, and how much of that time is spent on process 3 if we know outputs and goal outputs.
I would like to know if this is even possible to calculate. Any suggestion is more than welcome. Thanks in advance!
EDIT: I can't say that order 1 has been worked on for 8 hours because 3 more orders have been worked on by the same worker. For example, I can say that order 1 lasted from 6:00:00 to 6:15:00 and from 13:00:00 to 14:00:00 because none of the other orders were worked on in that interval.
The next interval is 6:15:00-11:30:00 where both order 1 and order 2 have been worked on. That interval is 5 hours and 15 minutes long. How much of that time can I join to order 1 and how much to order 2 with the parameters I have. The more I think about it, the more impossible it is to me.