How can I select a winning match only once and exclude it from being subsequently considered?

97 views Asked by At

I have multiple shipments of an item coming in. I have way more orders for the given item than I can supply. I've come up with a prioritization system that ranks how an order best fits with a given shipment (taking into account when they ordered, when they requested to receive their order and their priority status as a customer). I've created a view that ties every order with the incoming shipments with that order's priority score/rank for the given shipment. I can easily determine the first shipments winning orders but I'm having trouble excluding the winning orders from being selected as the winner for subsequent shipments. This is an issue because if a given order is ranked highest for the first shipment, it likely is ranked highest for the subsequent shipments. One of the major restrictions for doing this is that we want a live look at what orders are winning for shipments because end users will have the ability to make adjustments to the priority score/rank algorithm on the fly and they want to see the resulting changes. If this is not possible, I can easily create a table that would hold the winner and loop through shipments and deal with it that way but I'm hopeful that there is a way.

I want to provide a very simple example of what I'm trying to do. I have way more logic to different scenarios (e.g. handling orders that have a higher quantity than the shipment can supply, etc.) but that's not the issue I'm struggling with.

SHIPMENTS (Table)

SHIPMENT QUANTITY
A 10
B 10
C 20

ORDERS (Table)

ORDERID QUANTITY
1 5
2 5
3 10
4 20
5 20

ORDER_SHIPMENT_RANK (View)

SHIPMENT ORDERID SHIP_QTY ORDER_QTY RANK
A 1 10 5 1000
A 2 10 5 900
A 3 10 10 500
A 4 10 20 400
A 5 10 20 100
B 1 10 5 1200
B 2 10 5 1100
B 3 10 10 800
B 4 10 20 500
B 5 10 20 200
C 1 20 5 1500
C 2 20 5 1300
C 3 20 10 1000
C 5 20 20 900
C 4 20 20 600

*curve ball with Shipment C order 5 has a higher rank than order 4

Desired Results:

SHIPMENT ORDERID ORDER_QTY
A 1 5
A 2 5
B 3 10
C 5 20

Order 4 never wins because it's priority score/rank was never high enough on a given shipment.

I've tried working with analytic functions but have no idea how I can remove the subsequent iterations of an order on subsequent shipments.

I've tried working with analytic functions to select the top orders that can be filled with the shipments incoming quantity but I haven't been able to remove the subsequent iterations of an order that wins on a prior shipment from being considered for the subsequent shipments.

This should help you play with this issue:

WITH SHIPMENTS AS (
     SELECT 'A' SHIPMENT, 10 QUANTITY FROM dual UNION ALL
     SELECT 'B' SHIPMENT, 10 QUANTITY FROM dual UNION ALL
     SELECT 'C' SHIPMENT, 20 QUANTITY FROM dual
     ),
     
     ORDERS AS (
     SELECT '1' ORDERID, 5 QUANTITY FROM dual UNION ALL
     SELECT '2' ORDERID, 5 QUANTITY FROM dual UNION ALL
     SELECT '3' ORDERID, 10 QUANTITY FROM dual UNION ALL
     SELECT '4' ORDERID, 20 QUANTITY FROM dual UNION ALL
     SELECT '5' ORDERID, 20 QUANTITY FROM dual
     ),
     
     ORDER_SHIPMENT_RANK AS ( --I'm only including this here for convenience
     SELECT 'A' SHIPMENT, '1' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1000 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '2' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 900 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '3' ORDERID, 10 SHIP_QTY, 10 ORDER_QTY, 500 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '4' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 400 RANK FROM dual UNION ALL
     SELECT 'A' SHIPMENT, '5' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 100 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '1' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1200 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '2' ORDERID, 10 SHIP_QTY, 5 ORDER_QTY, 1100 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '3' ORDERID, 10 SHIP_QTY, 10 ORDER_QTY, 800 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '4' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 500 RANK FROM dual UNION ALL
     SELECT 'B' SHIPMENT, '5' ORDERID, 10 SHIP_QTY, 20 ORDER_QTY, 200 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '1' ORDERID, 20 SHIP_QTY, 5 ORDER_QTY, 1500 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '2' ORDERID, 20 SHIP_QTY, 5 ORDER_QTY, 1300 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '3' ORDERID, 20 SHIP_QTY, 10 ORDER_QTY, 1000 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '5' ORDERID, 20 SHIP_QTY, 20 ORDER_QTY, 900 RANK FROM dual UNION ALL
     SELECT 'C' SHIPMENT, '4' ORDERID, 20 SHIP_QTY, 20 ORDER_QTY, 600 RANK FROM dual 
     )
     
--This is just a base example/sample of something I was trying to leverage to determine the winning order
-- quantities that would be used BUT it returns the same order for multiple shipments (the issue at hand)
     SELECT    x.SHIPMENT
               ,x.ORDERID
               ,x.SHIP_QTY
               ,x.ORDER_QTY
               ,x.PREV_WINNER_QTY
               ,CASE WHEN x.SHIP_QTY - NVL(x.PREV_WINNER_QTY,0) > 0  THEN
                     LEAST(x.SHIP_QTY - NVL(x.PREV_WINNER_QTY,0) , x.ORDER_QTY) 
                ELSE 0
                END WINNING_QTY
     FROM      (
               SELECT    sr.*
                         ,COALESCE(
                         SUM(ORDER_QTY) OVER (PARTITION BY SHIPMENT ORDER BY SHIPMENT, RANK DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                         ,0
                         ) PREV_WINNER_QTY
               FROM      ORDER_SHIPMENT_RANK sr
               ) x;
               
0

There are 0 answers