Following a previous question, I now need to dive a little deeper:
I have two tables (workspace is attached):
table1 contains the columns "duration", "WP_1", "WP_2":
table 2 contains the columns "WP1", "WP2", "WC":
I need to split WP_1 and WP_2 in table 1 according to WP1 and WP2 in table2 - what does that mean?
Duration and WC need to be matched, but the ranges of WP_1 & WP_2 and WP1 & WP2 sometimes don't fit.
Let me give you an example:
- row3 in table1 for WP_1 and WP_2 has a range of 5 to 115 with a duration of 552.8
--> this perfectly fits into row1 of table2, where we have a range of 0 to 145 with a WC of 0
--> therefor 100% of the duration in table1 can be connected to WC 0
- row4 in table1 for WP_1 and WP_2 has a range of 115 to 219 with a duration of 584.7
--> does not fit in just one category:
row1 in table2: range for WP1 and WP2 is 0 to 145 with WC 0
row2 in table2: range for WP1 and WP2 is 145 to 169 with WC 1
row3 in table2: range for WP1 and WP2 is 169 to 1693 with WC 1
- split the WP_1 and WP_2 according to WP1 and WP2 and calculate their new duration
- duration will be calculated with the according parts
- WP_1 and WP_2 in table1's row4 will be split and new rows will be inserted:
--> original row4: WP_1: 115 WP_2: 219 duration: 584.7 --> 104 parts (219-115); 1 part =~ 5.62
--> new row4: WP_1: 115 WP_2:145 duration: 168.66 (30 parts (145-115) x 5.62)
--> new row5: WP_1:145 WP_2: 169 duration: 134.93 (24 parts (169-145) x 5.62)
--> new row6: WP_1:169 WP_2: 219 duration: 281.1 (50 parts (219-169) x 5.62)
the new table1 should look like that:
The main problem is the matching and splitting part - calculating the new durations should be quite easy.
Unfortunately as of writing this I have no clue how to match and split - any hints are very much appreciated!
Thanks for your help!