Oracle SQL Query for Distributing Quantity

I have one table called INVENTORY which has data:

ITEM_NUM SUB_INV LOT_NUM QUANTITY LOT_EXPIRY_DATE
101      22      A1      10       9/29/2019 
101      22      A2      14       9/28/2019 

Let’s say, I have a request to fetch the quantity of 20 from these LOTs. As per the table, I have a total quantity available 24 (10 + 14). So I have enough quantity. I can take 10 Quantities from A1 and 10 from A2. But Instead, I have to look at the LOT_EXPIRY_DATE. Since A2 is expiring first, I shall take 14 quantities from A2 and then the rest of the quantities i.e. 6 from A1.

So below query works just fine:

SELECT
     lot_num,
     item_num,
     quantity,
     lot_expiry_date,
     CASE
         WHEN req_quantity >= rolling_qty THEN quantity
         ELSE req_quantity - LAG(rolling_qty) OVER(
             ORDER BY
                 lot_expiry_date
         )
     END AS qty_fetched
 FROM
     (
         WITH base_inv_balance AS (
             SELECT
                 item_num,
                 lot_num,
                 quantity,
                 lot_expiry_date
             FROM
                 inventory
             WHERE
                 item_num = '101'
                 AND sub_inv = '22'
         ),requested_transaction AS (
             SELECT
                 '101' AS item_num,
                 20 AS req_quantity
             FROM
                 dual
         )
         SELECT
             base_inv_balance.item_num,
             base_inv_balance.lot_num,
             base_inv_balance.quantity,
             requested_transaction.req_quantity,
             base_inv_balance.lot_expiry_date,
             SUM(base_inv_balance.quantity) OVER(
                 PARTITION BY base_inv_balance.item_num
                 ORDER BY
                     base_inv_balance.lot_expiry_date
             ) AS rolling_qty
         FROM
             base_inv_balance
             JOIN requested_transaction ON base_inv_balance.item_num = requested_transaction.item_num
         ORDER BY
             base_inv_balance.item_num,
             base_inv_balance.lot_expiry_date
     )

Result:

LOT_NUM  ITEM_NUM QUANTITY LOT_EXPIRY_DATE QTY_FETCHED
A2       101      14       9/28/2019       14
A1       101      10       9/29/2019       6

But if the requested quantity is 13, then the result is

LOT_NUM  ITEM_NUM QUANTITY LOT_EXPIRY_DATE QTY_FETCHED
A2       101      14       9/28/2019       null
A1       101      10       9/29/2019       -1

Expected Result:

LOT_NUM  ITEM_NUM QUANTITY LOT_EXPIRY_DATE QTY_FETCHED
A2       101      14       9/28/2019       13
A1       101      10       9/29/2019       0

Is it possible to have a single query that can work well with both the quantities?

Need help with that query.

Answer

You want a cumulative sum and them some comparison logic:

select i.*,
       (case when running_quantity < 20 then quantity
             when running_quantity - quantity < 20 then 20 + quantity - running_quantity
             else 0
        end) as qty_fetched
from (select i.*
             sum(quantity) over (partition by item_num, sub_inv order by lot_expiry_date) as running_quantity
      from inventory i
      where item_num = 101 and sub_inv = 22
     ) i;

Here is a db<>fiddle.