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

user_3442912 0
Viewed 582

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
sql-server-2019 sql-server sql
2 Answers

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.

user_41160170

EDIT: Because I'm ambitious, I've now also found a solution with CTE. Please give my feedback if you find any bug or incorrect results. My old cursor solution is below.

New code with CTE:

DECLARE @OrderQty TABLE
   (OrderID INT NOT NULL,
    PID INT NOT NULL,
    CountOfOrder INT NOT NULL,
    StockQty INT NOT NULL,
    Qty INT NOT NULL,
    DeliverableOrderQty INT NOT NULL,
    PRIMARY KEY CLUSTERED(OrderID,PID))

INSERT INTO @OrderQty
     (OrderID, PID, CountOfOrder, StockQty, Qty, DeliverableOrderQty) 
SELECT o.OrderID,
     o.PID,
     foo.CountOfOrder,
     foo.StockQty,
     o.Qty,
     foo.StockQty / IIF(o.Qty = 0,1,o.Qty) AS DeliverableOrderQty
FROM   @Order AS o
INNER JOIN (SELECT o.PID,
               COUNT(DISTINCT o.OrderID) AS CountOfOrder,
               i.Qty AS StockQty,
               SUM(o.Qty) AS TotalOrderOty
         FROM   @Order AS o
         INNER JOIN @Inventory AS i ON o.PID = i.PID
         GROUP BY o.PID,
                i.Qty) AS foo ON o.PID = foo.PID

DECLARE @OrdersDeliverableQty TABLE
   (OrderID INT NOT NULL PRIMARY KEY,
    CountOfOrder INT NOT NULL,
    DeliverableQty INT NOT NULL)

INSERT INTO @OrdersDeliverableQty
     (OrderID, CountOfOrder, DeliverableQty) 
SELECT oq.OrderID,
     oq.CountOfOrder,
     MIN(oq.DeliverableOrderQty) AS DeliverableQty
FROM   @OrderQty AS oq
GROUP BY oq.OrderID,
       oq.CountOfOrder

DECLARE @AllOrders TABLE
   (OrderID INT NOT NULL PRIMARY KEY)

INSERT INTO @AllOrders
     (OrderID) 
SELECT o.OrderID
FROM   @Order AS o
GROUP BY o.OrderID

DECLARE @DeliverableOrder TABLE
   (OrderID INT NOT NULL PRIMARY KEY);

WITH CTE_1(RankID, OrderID, PID, StockQty, Qty)
    AS (SELECT RANK() OVER(
            ORDER BY oq.PID,
                    oq.DeliverableOrderQty DESC,
                    oq.Qty,
                    oq.OrderID) AS RankID,
            oq.OrderID,
            oq.PID,
            oq.StockQty,
            oq.Qty
       FROM   @OrderQty AS oq
       INNER JOIN @OrdersDeliverableQty AS ohmttoq ON oq.OrderID = ohmttoq.OrderID
                                            AND oq.DeliverableOrderQty = ohmttoq.DeliverableQty),
    CTE_2(MinRankID, MaxRankID)
    AS (SELECT MIN(c.RankID) AS MinRankID,
            MAX(c.RankID) AS MaxRankID
       FROM   CTE_1 AS c),
    CTE_3(NextRankID, MaxRankID, RankID, OrderID, PID, StockQty, RestQty, Qty)
    AS (SELECT c2.MinRankID + 1 AS NextRankID,
            c2.MaxRankID AS MaxRankID,
            c.RankID,
            c.OrderID,
            c.PID,
            c.StockQty,
            c.StockQty - c.Qty AS RestQty,
            c.Qty
       FROM     CTE_1 AS c
       INNER JOIN CTE_2 AS c2 ON c.RankID = c2.MinRankID
       UNION ALL
       SELECT c3.NextRankID + 1 AS NextRankID,
            c3.MaxRankID,
            c3.NextRankID,
            c1.OrderID,
            c1.PID,
            c1.StockQty,
            CASE
                   WHEN c3.PID = C1.PID
                   THEN c3.RestQty
                ELSE c1.StockQty
            END - c1.Qty AS RestQty,
            c1.Qty
       FROM   CTE_3 AS c3
       INNER JOIN CTE_1 AS c1 ON c3.NextRankID = c1.RankID
       WHERE  c3.NextRankID <= c3.MaxRankID)
    INSERT INTO @DeliverableOrder
         (OrderID) 
    SELECT c.OrderID
    FROM   CTE_3 AS c
    WHERE  c.RestQty >= 0

SELECT ao.OrderID,
     CASE
            WHEN oo.OrderID IS NULL
            THEN 'NOT Deliverable'
        ELSE 'Deliverable'
     END AS STATUS
FROM   @AllOrders AS ao
LEFT JOIN @DeliverableOrder AS oo ON ao.OrderID = oo.OrderID

Test Data:

DECLARE @Inventory TABLE
   (PID INT NOT NULL PRIMARY KEY,
   Qty INT NOT NULL)

DECLARE @Order TABLE
   (OrderID INT NOT NULL,
   PID INT NOT NULL,
   Qty INT NOT NULL,
   PRIMARY KEY CLUSTERED(OrderID,PID))

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

INSERT INTO @Order
     (OrderID, PID, Qty) 
VALUES (100,1,2), (100,2,2), (100,3,2),
       (200,1,2), (200,2,5), (200,3,1),
       (300,1,2), (300,2,2), (300,3,0),
       (400,1,2), (400,2,1), (400,3,2),
       (500,1,5), (500,2,5), (500,3,5),
       (600,1,1), (600,2,1), (600,3,1),
       (700,1,0), (700,2,1), (700,3,1)

Result:

OrderID Status
100     Deliverable
200     NOT Deliverable
300     Deliverable
400     NOT Deliverable
500     NOT Deliverable
600     Deliverable
700     Deliverable

If you need more information or explanations, leave a comment.

Old code with cursor:

DECLARE @OrderQty TABLE
   (OrderID INT NOT NULL,
    PID INT NOT NULL,
    CountOfOrder INT NOT NULL,
    StockQty INT NOT NULL,
    Qty INT NOT NULL,
    DeliverableOrderQty INT NOT NULL,
    PRIMARY KEY CLUSTERED(OrderID,PID))

INSERT INTO @OrderQty
   (OrderID, PID, CountOfOrder, StockQty, Qty, DeliverableOrderQty) 
SELECT o.OrderID,
      o.PID,
      foo.CountOfOrder,
      foo.StockQty,
      o.Qty,
      foo.StockQty / IIF(o.Qty = 0,1,o.Qty) AS DeliverableOrderQty
FROM   @Order AS o
INNER JOIN (SELECT o.PID,
                COUNT(DISTINCT o.OrderID) AS CountOfOrder,
                i.Qty AS StockQty,
                SUM(o.Qty) AS TotalOrderOty
         FROM   @Order AS o
         INNER JOIN @Inventory AS i ON o.PID = i.PID
         GROUP BY o.PID,
                   i.Qty) AS foo ON o.PID = foo.PID

DECLARE @OrdersDeliverableQty TABLE
   (OrderID INT NOT NULL PRIMARY KEY,
    CountOfOrder INT NOT NULL,
    DeliverableQty INT NOT NULL)

INSERT INTO @OrdersDeliverableQty
   (OrderID, CountOfOrder, DeliverableQty) 
SELECT oq.OrderID,
      oq.CountOfOrder,
      MIN(oq.DeliverableOrderQty) AS DeliverableQty
FROM   @OrderQty AS oq
GROUP BY oq.OrderID,
         oq.CountOfOrder

DECLARE @AllOrders TABLE
   (OrderID INT NOT NULL PRIMARY KEY)

INSERT INTO @AllOrders
      (OrderID) 
SELECT o.OrderID
FROM   @Order AS o
GROUP BY o.OrderID

DECLARE @DeliverableOrder TABLE
   (OrderID INT NOT NULL PRIMARY KEY)


DECLARE @OrderID INT,
      @PID INT,
      @StockQty INT,
      @Qty INT

DECLARE @LastPIDCursor INT

DECLARE @QtyRest INT

DECLARE order_qty_cursor CURSOR
FOR SELECT oq.OrderID,
        oq.PID,
        oq.StockQty,
        oq.Qty
   FROM   @OrderQty AS oq
   INNER JOIN @OrdersDeliverableQty AS ohmttoq ON oq.OrderID = ohmttoq.OrderID
                                        AND oq.DeliverableOrderQty = ohmttoq.DeliverableQty
   ORDER BY oq.PID,
          oq.DeliverableOrderQty DESC,
          oq.Qty


OPEN order_qty_cursor

FETCH NEXT FROM order_qty_cursor INTO @OrderID,
                              @PID,
                              @StockQty,
                              @Qty

WHILE @@Fetch_Status = 0
   BEGIN
      IF @LastPIDCursor IS NULL
        OR @LastPIDCursor <> @PID
         BEGIN
            SET @QtyRest = @StockQty - @Qty
      END
         ELSE
         BEGIN
            SET @QtyRest = @QtyRest - @Qty
      END

      IF @QtyRest >= 0
        AND NOT EXISTS (SELECT 1
                     FROM   @DeliverableOrder
                     WHERE  OrderID = @OrderID) 
         BEGIN
            INSERT INTO @DeliverableOrder
                  (OrderID) 
            VALUES
                  (@OrderID) 
      END

      SET @LastPIDCursor = @PID

      FETCH NEXT FROM order_qty_cursor INTO @OrderID,
                                    @PID,
                                    @StockQty,
                                    @Qty
   END

CLOSE order_qty_cursor

DEALLOCATE order_qty_cursor

SELECT ao.OrderID,
     CASE
            WHEN oo.OrderID IS NULL
            THEN 'NOT Deliverable'
        ELSE 'Deliverable'
     END AS STATUS
FROM   @AllOrders AS ao
LEFT JOIN @DeliverableOrder AS oo ON ao.OrderID = oo.OrderID
user_113050290
Related