Sql-server – Check Orders that can be delievered with reducing quantity from stock

sql serversql-server-2019

Just like any retail business we have an Orders table and an Inventory table. What I am trying to do is to check Orders for which we have enough stock available to dispatch. A few things I need to consider:

  1. If all the items in an order are available only then consider this order to be “Deliverable”

  2. Check Order's deliverable status in the order of OrderID (int value) .i.e OrderID 1 then 2 and so on.

  3. Before checking for deliverability of next order, reduce the available of stock for the next order. (not update the Inventory table but just take into account the stock quantity that has been already consumed by previous orders).

  4. If we do not have enough stock for 1 or more items in the order, completely ignore the order and do not reduce available stock quantity for the next order to be checked.

In the following example:

  1. Order 100 is fully Deliverable because we have enough stock for all products.
  2. Order 200 is not fully Deliverable because PID 2 requires Qty 5 but we only have 3 left after 2 being consumed by the Order 100
  3. Finally, Order 300 is also fully Deliverable because we have enough stock for all products.

Test Data

INSERT INTO @Inventory(PID, Qty)
VALUES  (1  , 10) 
    ,   (2  , 5) 
    ,   (3  , 2)


INSERT INTO @Order (OrderID, PID, Qty)
VALUES  (100 , 1 , 2)   --\
    ,   (100 , 2 , 2)   ----> This order is fully available
    ,   (100 , 3 , 1)   --/

    ,   (200 , 1 , 2)   --\
    ,   (200 , 2 , 5)   ----> This order is not fully available
    ,   (200 , 3 , 1)   --/     because of PID 2 only 3 QTY left

    ,   (300 , 1 , 2)   --\
    ,   (300 , 2 , 2)   ----> This order is fully available
    ,   (300 , 3 , 1);  --/

Expected Output

OrderID Status
100     Deliverable
200     NOT Deliverable
300     Deliverable

My Attempt

I know that it is far from the actual solution but I still wanted to share what I have been trying 🙂

WITH OrderCTE AS 
    (
        SELECT DENSE_RANK() OVER (ORDER BY OrderID) AS OrderRN
            , OrderID
            , PID
            , Qty
        FROM @Order
    )
,CTE AS
(
    SELECT o.OrderID
         , o.PID
         , o.Qty
         , i.Qty - o.Qty AS QtyAvailable
         , o.OrderRN  AS OrderRN
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    WHERE o.OrderID IN (
                            SELECT TOP 1 o.OrderID
                            FROM @Order o
                            WHERE NOT EXISTS ( SELECT 1 FROM @Inventory i WHERE i.PID = o.PID AND i.Qty < o.Qty)
                            ORDER BY o.OrderID
                        )

    UNION ALL

    SELECT o.OrderID
         , o.PID
         , o.Qty
         , o.Qty - c.QtyAvailable
         , c.OrderRN + 1
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    INNER JOIN CTE c ON c.OrderRN + 1 = o.OrderRN AND c.PID = o.PID
    WHERE o.Qty <= c.QtyAvailable

)
SELECT * 
FROM CTE

Best Answer

I'm not a big fan of cursor but it seems like a good case for it.

Declare OrderCursor Cursor
Local Static Read_Only Forward_only 
For select distinct OrderID from @Order order by OrderID;

declare @OrderID INT;
create table #Remaining_Inventory  (PID int, Qty int);
create table #Result (OrderID int, Stat varchar(20));

insert into #Remaining_Inventory 
select * from @Inventory;

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID;  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    
    if not exists (
    select 1
    from @Order o
    left join #Remaining_Inventory i on o.PID = i.PID and o.Qty <= i.Qty
    where o.orderid = @OrderID
    and i.PID is null
    )
        begin
            insert into #Result select @OrderID, 'Deliverable';
            update #Remaining_Inventory set #Remaining_Inventory.Qty= #Remaining_Inventory.Qty-o.Qty
            from #Remaining_Inventory 
            join @order o on #Remaining_Inventory.PID = o.PID;
        end
    else
        insert into #Result select @OrderID, 'Not Deliverable';

        FETCH NEXT FROM OrderCursor INTO @OrderID
    END  
  
    select * from #Result;

    drop table #Remaining_Inventory;
    drop table #Result;
    CLOSE OrderCursor
    DEALLOCATE OrderCursor

will return the expected result.

Related Question