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.
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.
So, here's what I
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