Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
See page 17-19 of this document: IntervalMatch and Slowly Changing Dimensions
See page 17-19 of this document: IntervalMatch and Slowly Changing Dimensions
Hey Gysbert thank you for your quick help.
There is even an example for exactly my case(mentioned on page 19.
Awesome! Thank you very much.