## Question and Answer

## You Asked

Hi Chris and Connor,

I have two tables t1 - Stores warehouse, item, and percentage, t2 - Stores warehouse, item, order, line, quantity, and request date.

The hard code value 9 (will be as input to query) is the quantity that I want to assign to table t2 rows based on the percentage given in table t1.

I also need a new column called status which will show Y if an assignment is done and N if an assignment is not done, and new it should generate rows for the quantity which is leftover with status P i.e. pending unassigned quantity.

The assigned quantity should be in integer, in case of a tie on request date - we must give priority based on order and line. For the cases where full input quantity can’t be assigned the last row of table t2 can have less/more quantity.

I have attempted a solution but it doesn’t show the desired result. I have provided sample data as below.

Desired result -

I have two tables t1 - Stores warehouse, item, and percentage, t2 - Stores warehouse, item, order, line, quantity, and request date.

The hard code value 9 (will be as input to query) is the quantity that I want to assign to table t2 rows based on the percentage given in table t1.

I also need a new column called status which will show Y if an assignment is done and N if an assignment is not done, and new it should generate rows for the quantity which is leftover with status P i.e. pending unassigned quantity.

The assigned quantity should be in integer, in case of a tie on request date - we must give priority based on order and line. For the cases where full input quantity can’t be assigned the last row of table t2 can have less/more quantity.

I have attempted a solution but it doesn’t show the desired result. I have provided sample data as below.

DROP TABLE t1; CREATE TABLE t1 ( organization_code VARCHAR2(3), item_number VARCHAR2(50), share_percentage NUMBER); INSERT INTO t1 ( SELECT 'W1', 'A', 40 FROM DUAL UNION ALL SELECT 'W2', 'A', 10 FROM DUAL UNION ALL SELECT 'W3', 'A', 10 FROM DUAL UNION ALL SELECT 'W4', 'A', 10 FROM DUAL UNION ALL SELECT 'W5', 'A', 10 FROM DUAL UNION ALL SELECT 'W6', 'A', 10 FROM DUAL UNION ALL SELECT 'W7', 'A', 10 FROM DUAL ); DROP TABLE t2; CREATE TABLE t2 ( organization_code VARCHAR2(3), order_number NUMBER, line_number NUMBER, item_number VARCHAR2(50), ordered_quantity NUMBER, request_date DATE); INSERT INTO t2 ( SELECT 'W1', 1, 1, 'A', 10, TIMESTAMP '2021-02-01 10:10:10' FROM DUAL UNION ALL SELECT 'W2', 2, 1, 'A', 10, TIMESTAMP '2021-02-01 11:10:10' FROM DUAL UNION ALL SELECT 'W3', 3, 1, 'A', 10, TIMESTAMP '2021-02-01 12:10:10' FROM DUAL UNION ALL SELECT 'W4', 4, 1, 'A', 10, TIMESTAMP '2021-02-01 13:10:10' FROM DUAL UNION ALL SELECT 'W5', 5, 1, 'A', 10, TIMESTAMP '2021-02-01 14:10:10' FROM DUAL UNION ALL SELECT 'W6', 6, 1, 'A', 10, TIMESTAMP '2021-02-01 15:10:10' FROM DUAL UNION ALL SELECT 'W7', 7, 1, 'A', 10, TIMESTAMP '2021-02-01 16:10:10' FROM DUAL ); SELECT organization_code AS organization_code , order_number AS order_number , line_number AS line_number , item_number AS item_number , to_share_percentage - LAG(to_share_percentage, 1, 0) OVER( PARTITION BY organization_code , item_number ORDER BY request_date) AS shared_percentage , 1 + LAG(to_share_percentage, 1, 0) OVER (PARTITION BY organization_code , item_number ORDER BY request_date) AS from_share_percent , to_share_percentage AS to_share_percentage FROM ( SELECT t2.organization_code , t2.order_number , t2.line_number , t2.item_number , t2.ordered_quantity , t2.request_date , ROUND (9 * SUM(share_percentage) OVER( PARTITION BY t2.organization_code , t2.item_number ORDER BY t2.request_date) / 100) AS to_share_percentage FROM t1 t1 , t2 t2 WHERE t1.organization_code = t2.organization_code AND t1.item_number = t2.item_number ) ORDER BY 1;

Desired result -

ORGANIZATION ORDER_NUMBER LINE_NUMBER ITEM_NUMBER SHARED_PERCENTAGE STATUS W1 1 1 A 4 Y -- 40% of 9 is 3.6 so 4 is mapped to this row. W1 1 1 A 6 N -- Order quantity was 10 but assigned is 40% of 9 so left over is this row. W2 2 1 A 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row. W2 2 1 A 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row. W3 3 1 A 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row. W3 3 1 A 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row. W4 4 1 A 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row. W4 4 1 A 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row. W5 5 1 A 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row. W5 5 1 A 9 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row. W6 6 1 A 1 Y -- 10% of 9 is 0.9 so 1 is mapped to this row. W6 6 1 A 1 N -- Order quantity was 10 but assigned is 10% of 9 so left over is this row. W7 7 1 A 1 P -- Last row with status P becuase out of nothing left to assign here.

## and we said...

So, here's what I

- 9 represents some form of "required" quantity

- You need to split this between the items according to the listed percentage (rounding fractional digits up)

- Calculate the running total of item percentages used, compare this to the required quantity

- If the running total <= required quantity (9), generated two rows:

* The first is status = Y that has the calculated pct

* The other is status = N that has (item) ordered quantity - calculated pct

- Once the running total > required quantity, only have one row/item with status = P

If so, you can do this as follows:

- Assign row numbers to the data in the order you want to allocate the quantities

- Use recursive with to calculate the running total of the quantity used so far (I doubt you can do this with analytic functions due to the rounding issues)

- Union all the results of this together:

* The first branch checks if the allocated quantity < required quantity, setting Y if it is and P if it isn't

* The second only returns rows where the outstanding quantity >= 0; this has status N and subtracts the allocated pct for this row from the ordered quantity for this row

Which looks something like the query below.

I'm not convinced this is exactly what you're looking for and there are various edge cases to consider; hopefully this is enough to get you started though:

*think*you're looking for:- 9 represents some form of "required" quantity

- You need to split this between the items according to the listed percentage (rounding fractional digits up)

- Calculate the running total of item percentages used, compare this to the required quantity

- If the running total <= required quantity (9), generated two rows:

* The first is status = Y that has the calculated pct

* The other is status = N that has (item) ordered quantity - calculated pct

- Once the running total > required quantity, only have one row/item with status = P

If so, you can do this as follows:

- Assign row numbers to the data in the order you want to allocate the quantities

- Use recursive with to calculate the running total of the quantity used so far (I doubt you can do this with analytic functions due to the rounding issues)

- Union all the results of this together:

* The first branch checks if the allocated quantity < required quantity, setting Y if it is and P if it isn't

* The second only returns rows where the outstanding quantity >= 0; this has status N and subtracts the allocated pct for this row from the ordered quantity for this row

Which looks something like the query below.

I'm not convinced this is exactly what you're looking for and there are various edge cases to consider; hopefully this is enough to get you started though:

with rws as ( select organization_code, item_number, share_percentage, ordered_quantity, row_number () over ( order by request_date ) rn, 9 req_quant from t1 join t2 using ( organization_code, item_number ) ), tree ( organization_code, item_number, share_percentage, ordered_quantity, rn, req_quant, quantity_consumed, tot_consumed ) as ( select r.*, ceil ( req_quant * share_percentage / 100 ) quantity_consumed, ceil ( req_quant * share_percentage / 100 ) tot_consumed from rws r where rn = 1 union all select r.organization_code, r.item_number, r.share_percentage, r.ordered_quantity, r.rn, r.req_quant, ceil ( r.req_quant * r.share_percentage / 100 ) quantity_consumed, t.tot_consumed + ceil ( r.req_quant * r.share_percentage / 100 ) from tree t join rws r on t.rn + 1 = r.rn ), used as ( select t.*, req_quant - tot_consumed remaining from tree t ) select organization_code, item_number, share_percentage, ordered_quantity, req_quant, quantity_consumed, tot_consumed, case when req_quant >= tot_consumed then 'Y' else 'P' end status, 1 sort_pos from used union all select organization_code, item_number, share_percentage, ordered_quantity, req_quant, ordered_quantity - quantity_consumed, tot_consumed, 'N' status, 2 sort_pos from used where remaining >= 0 order by 1, 2, sort_pos; ORGANIZATION_CODE ITEM_NUMBER SHARE_PERCENTAGE ORDERED_QUANTITY REQ_QUANT QUANTITY_CONSUMED TOT_CONSUMED STATUS SORT_POS W1 A 40 10 9 4 4 Y 1 W1 A 40 10 9 6 4 N 2 W2 A 10 10 9 1 5 Y 1 W2 A 10 10 9 9 5 N 2 W3 A 10 10 9 1 6 Y 1 W3 A 10 10 9 9 6 N 2 W4 A 10 10 9 1 7 Y 1 W4 A 10 10 9 9 7 N 2 W5 A 10 10 9 1 8 Y 1 W5 A 10 10 9 9 8 N 2 W6 A 10 10 9 1 9 Y 1 W6 A 10 10 9 9 9 N 2 W7 A 10 10 9 1 10 P 1

## Rating

# Comments

Team:

How about a (simple) model clause like this avoiding many layers of CTE's.

How about a (simple) model clause like this avoiding many layers of CTE's.

demo@XEPDB1> select t1.*, 2 case when r2 = 1 and req_quant >= tot_consumed then 'Y' 3 when r2 = 1 and req_quant < tot_consumed then 'P' 4 else 'N' end as status 5 from ( 6 select organization_code, item_number, share_percentage,ordered_quantity,req_quant, qty_consumed,tot_consumed,remaining 7 from ( 8 select organization_code, item_number, share_percentage, ordered_quantity, 9 row_number() over( order by t2.request_date ) r, 10 9 req_quant 11 from t1 12 join t2 13 using ( organization_code, item_number ) 14 ) 15 model 16 dimension by ( r ) 17 measures( organization_code,item_number,share_percentage,ordered_quantity, 18 req_quant, 0 as qty_consumed,0 as tot_consumed, 0 as remaining) 19 ( qty_consumed[any] = ceil( (req_quant[cv()] * share_percentage[cv()])/100 ) , 20 tot_consumed[any] = sum( qty_consumed ) [ r<=cv() ], 21 remaining[any] = req_quant[cv()] - tot_consumed[cv()] 22 ) 23 ) t1, lateral( select level r2 24 from dual 25 connect by level <= case when remaining >= 0 then 2 else 1 end ) 26 order by 1,r2 27 / ORG ITEM_NUMBE SHARE_PERCENTAGE ORDERED_QUANTITY REQ_QUANT QTY_CONSUMED TOT_CONSUMED REMAINING S --- ---------- ---------------- ---------------- ---------- ------------ ------------ ---------- - W1 A 40 10 9 4 4 5 Y W1 A 40 10 9 4 4 5 N W2 A 10 10 9 1 5 4 Y W2 A 10 10 9 1 5 4 N W3 A 10 10 9 1 6 3 Y W3 A 10 10 9 1 6 3 N W4 A 10 10 9 1 7 2 Y W4 A 10 10 9 1 7 2 N W5 A 10 10 9 1 8 1 Y W5 A 10 10 9 1 8 1 N W6 A 10 10 9 1 9 0 Y W6 A 10 10 9 1 9 0 N W7 A 10 10 9 1 10 -1 P 13 rows selected. demo@XEPDB1>

I'm not sure simple and MODEL go together ;)

That seems to work - though my understanding is for N rows, QTY_CONSUMED should be ORDERED_QUANTITY - QTY_CONSUMED for the corresponding Y row

That seems to work - though my understanding is for N rows, QTY_CONSUMED should be ORDERED_QUANTITY - QTY_CONSUMED for the corresponding Y row