Hi all,
The requirement is to fill the buckets (in the data table) in the order (mentioned in the priority table) by adjusting the quantity present in the data table.
The below table stores the priority in the order in which I must fill the buckets.
CREATE TABLE priority AS SELECT * FROM
(SELECT 'A' bucket, 1 priority FROM DUAL UNION ALL
SELECT 'B', 2 FROM DUAL UNION ALL
SELECT 'C', 3 FROM DUAL)
The below table stores the quantity of items to fill in the bucket.
CREATE TABLE volumes AS SELECT * FROM
(SELECT 'P1' item, 8 quantity FROM DUAL UNION ALL
SELECT 'P2', 8 FROM DUAL)
The below table stores the data for filling the buckets. The column quantity denotes the present quantity of the item in the bucket and we need to adjust it based on the quantity present in the volume table.
CREATE TABLE data AS SELECT * FROM
(
SELECT 'A' bucket, 'P1' item, 6 present_qty FROM DUAL UNION ALL
SELECT 'C' , 'P1' , 7 FROM DUAL UNION ALL
SELECT 'B' , 'P1' , 2 FROM DUAL UNION ALL
SELECT 'C' , 'P2' , 1 FROM DUAL UNION ALL
SELECT 'A' , 'P2' , 7 FROM DUAL UNION ALL
SELECT 'B' , 'P2' , 9 FROM DUAL);
Below is the expected outcome and the explanation of to get the outcome -
SELECT 'A' bucket, 'P1' item, 6 present_qty, 2 add_qty, 0 subtract_qty, 8 result_qty, 'Y' status, 'B' add_from, NULL add_to FROM DUAL UNION ALL
SELECT 'B' , 'P1' , 2 , 0 , 2 , 0 , 'N' , NULL , 'A' FROM DUAL UNION ALL
SELECT 'C' , 'P1' , 7 , 0 , 0 , 2 , 'N' , NULL , NULL FROM DUAL UNION ALL
SELECT 'A' , 'P2' , 7 , 1 , 0 , 8 , 'Y' , 'B' , NULL FROM DUAL UNION ALL
SELECT 'B' , 'P2' , 9 , 0 , 1 , 8 , 'Y' , NULL , 'A' FROM DUAL UNION ALL
SELECT 'C' , 'P2' , 1 , 0 , 0 , 1 , 'N' , NULL , NULL FROM DUAL;
Outcome row one:
The column bucket, item, present_qty values will remain the same as they are in the data table, and we only sorted them in the order given in the priority table.
Column add_qty has value 2 because the present_qty (2) of bucket B and item P1 is added here to make result_qty as 8 (fill 8 of P1 in each bucket as stored in volume table for P1)
Column subtract_qty is NULL becuase we didn't give any quantity from this bucket row to another.
Column result_qty has value 8 because because present_qty (6) + present_qty (2) of bucket B and item P1 = 8
Column status has the value 'Y' because the bucket is filled.
Column add_from has value B because we took 2 from bucket B, item P1 row to completely fill the bucket with 8 quantities.
Column add_to has value NULL because we didn't take any quantity from this bucket to other buckets.