Get Orders status that can be delivered with reducing quantity from stock without curson or While loop

Question

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

Answer 1

The method below doesn't produce correct results. When I put all pieces together I got:

+---------+--------------------+
| OrderID | OrderIsDeliverable |
+---------+--------------------+
|     100 |                  1 |
|     200 |                  0 |
|     300 |                  0 |
+---------+--------------------+

Order=300 was marked as non deliverable, because my query processes all Products independently and this is not correct. The previous Order=200 hogged the quantity for PID=3, even though this Order=200 was not deliverable overall (based on Products other than PID=3) and it should not affect the following orders. But it did affect the following orders, which is not correct.

I don't see how to write a single query without explicit loop(s) here.

Alas.

---

You can simulate a loop using recursive CTE.

I will show you a query that does the core thing and leave the rest to you, because overall it becomes too long.

The main idea - you need a running total that resets when it reaches a threshold. There are many questions on this topic, I used this as a basis for my answer.

In the query below I'm looking only at a slice of your data, only at one specific PID = 2.

CTE_RN gives us row numbers to iterate upon. CTE_Recursive is the main loop which checks if the running total exceeds the limit. If it does, it discards the Qty from that Order and sets the OrderIsDeliverable flag.

Query

WITH
CTE_RN
AS
(
    SELECT
        O.OrderID
        ,O.PID
        ,O.Qty
        ,I.Qty AS LimitQty
        ,ROW_NUMBER() OVER (ORDER BY O.OrderID) AS rn
    FROM
        @Order AS O
        INNER JOIN @Inventory AS I ON I.PID = O.PID
    WHERE O.PID = 2 -- this would become a parameter
)
,CTE_Recursive
AS
(
    SELECT
        CTE_RN.OrderID
        ,CTE_RN.PID
        ,CTE_RN.Qty
        ,CTE_RN.LimitQty
        ,CTE_RN.rn
        -- this would generate a simple running total
        --,CTE_RN.Qty AS SumQty

        -- the very first order may exceed the limit
        ,CASE WHEN CTE_RN.Qty > CTE_RN.LimitQty
        THEN 0
        ELSE CTE_RN.Qty
        END AS SumQty
        ,CASE WHEN CTE_RN.Qty > CTE_RN.LimitQty
        THEN 0
        ELSE 1
        END AS OrderIsDeliverable
    FROM
        CTE_RN
    WHERE
        CTE_RN.rn = 1

    UNION ALL

    SELECT
        CTE_RN.OrderID
        ,CTE_RN.PID
        ,CTE_RN.Qty
        ,CTE_RN.LimitQty
        ,CTE_RN.rn
        -- this would generate a simple running total
        --,CTE_RN.Qty + CTE_Recursive.SumQty AS SumQty

        -- check if running total exceeds the limit
        ,CASE WHEN CTE_RN.Qty + CTE_Recursive.SumQty > CTE_RN.LimitQty
        THEN CTE_Recursive.SumQty -- don't increase the running total
        ELSE CTE_RN.Qty + CTE_Recursive.SumQty
        END AS SumQty
        ,CASE WHEN CTE_RN.Qty + CTE_Recursive.SumQty > CTE_RN.LimitQty
        THEN 0
        ELSE 1
        END AS OrderIsDeliverable
    FROM
        CTE_RN
        INNER JOIN CTE_Recursive ON CTE_Recursive.rn + 1 = CTE_RN.rn
)
SELECT * FROM CTE_Recursive
;

Result

+---------+-----+-----+----------+----+--------+--------------------+
| OrderID | PID | Qty | LimitQty | rn | SumQty | OrderIsDeliverable |
+---------+-----+-----+----------+----+--------+--------------------+
|     100 |   2 |   2 |        5 |  1 |      2 |                  1 |
|     200 |   2 |   5 |        5 |  2 |      2 |                  0 |
|     300 |   2 |   2 |        5 |  3 |      4 |                  1 |
+---------+-----+-----+----------+----+--------+--------------------+

---

Now you need to run this query for each PID. I would wrap this query into a table-valued function with parameter and pass PID as parameter. Maybe you can do it without a function as well. Obviously, to create a function you can't have table variables, you need actual tables to reference in your function, so adjust the code accordingly.

Then call it something like this:

SELECT
    ...
FROM
    @Inventory AS I
    CROSS APPLY dbo.MyFunc(I.PID) AS A

This would return the same number of rows as in the @Order table. Then you need to group this by OrderID and look at the OrderIsDeliverable flag. If this flag is 0 at least once for an Order, this Order is not deliverable.

Something like this:

SELECT
    A.OrderID
    ,MIN(OrderIsDeliverable) AS OrderIsDeliverable
FROM
    @Inventory AS I
    CROSS APPLY dbo.MyFunc(I.PID) AS A
GROUP BY
    A.OrderID
;

---

Ideally, you should try various approaches (cursor, recursive CTE, etc.), make sure that you have appropriate indexes, measure their performance on your real data and hardware and decide which one to use.