Hi,
I have 2 tables. Lets call them OUTAGES and PRODUCTION.
To keep it "simple" only the necessary values.
PRODUCTION:
| Key | OrderID | Start | Stop | Production time (h) |
|---|
| 1 | 12345 | 12:00 | 14:00 | 2 |
| 1 | 12346 | 14:00 | 20:00 | 6 |
| 3 | 12350 | 08:00 | 16:00 | 8 |
| 4 | 12346 | 12:00 | 16:00 | 4 |
| 5 | 12350 | 13:00 | 17:00 | 4 |
OUTAGES:
| Key | Start | Stop | Outage Duration (h) |
|---|
| 1 | 14:00 | 15:00 | 1 |
| 3 | 14:00 | 17:00 | 3 |
| 5 | 13:00 | 13:30 | 0.5 |
Now what I want to get is the net effective production time.
Production Time - Outage Duration
PRODUCTION(FINAL):
| Key | OrderID | Start | Stop | Production time | Eff Production Time (h) | Outage |
|---|
| 1 | 12345 | 12:00 | 14:00 | 2 | 2 | No |
| 1 | 12346 | 14:00 | 20:00 | 6 | 5 | Yes |
| 3 | 12350 | 08:00 | 16:00 | 8 | 6(because 1 hour is not in this time interval) | Yes |
| 4 | 12346 | 12:00 | 16:00 | 4 | 4 | No |
| 5 | 12350 | 13:00 | 17:00 | 4 | 3.5 | Yes |
Unfortunately I only have a "indirect" link through the key. The case like Key3 happens not very often but it happens...
I think I want to solve this in script, but any solution is appreciated.
Thanks in advance and best regards,
Peter
