I have two Tables and I need to fulfill table 2 Need_qty with minimum movement from table 1 sending_qty.
Table 1
sending_QTY STORE_ID_A
30 30105
16 21168
10 21032
9 30118
6 30011
5 21190
2 21016
Table 2
Need_QTY STORE_ID_B
15 21005
10 30019
11 21006
16 30001
11 21015
7 21004
Expected output
STORE_ID_A |STORE_ID_B |TRANSFERRED_QTY_FROM_A |
-----------|-----------|-----------------------|
30105 |21005 |15 |
30105 |30019 |10 |
30105 |21006 |5 |
21168 |21006 |6 |
21168 |30001 |10 |
21032 |30001 |6 |
21032 |21015 |4 |
30118 |21015 |7 |
30118 |21004 |2 |
30011 |21004 |5 |
There are several other combinations to achieve this but I need to find minimum possible transfer so that table 2 need_qty gets fullfill Is there any way to achieve this without procedural approach?
So far I have tried to cross join to find the combinations but didn't help much