Insert only up to a maximum number?

Question

I have an existing stored procedure that returns shopping cart items as individual rows, with one of the fields being Quantity of the said item.

In the case of a promotional offer, for example buy 2 and get another 2 free, then the same product will be returned as two rows, with a separate price and a quantity for each. In this case, 2 rows with a quantity of 2 in each row.

The procedure then checks each row against the quantity in stock, and alters the row if required by inserting into a new table for the updated order. Normally there are many items of each product in stock at a time, but in rare situations (normally for end run products) there could be fewer items in stock than the cart total should allow.

In this example, lets say there's 3 left. The procedure would compare each row as normal and see that there are 3 in stock and the row is only looking for 2. So each row passes the check even though we're one short.

The problem I have is, how can I keep a running total of how many items are in the cart when each row is compared individually? Can I declare and update an int value for every insert?

Edit Sample Data to illustrate issue:

Lets say that order number 1 returns the same product on 3 rows. Full price, discounted and free (probably not a realistic situation but one that my boss will want me to account for anyway)

LineID  |  ProductID  |  Price   |  Quantity  | Note
001     |    00001    |   100    |    2       |
002     |    00001    |    50    |    2       |
003     |    00001    |     0    |    2       |

These rows are in a temp table item which is joined to the products products table by ProductID which would look something like this:

ProductID | BasePrice | QuantityAvailable
00001     |     100   |       3

Then the products are checked with this query:

UPDATE item 
        SET 
            note = 
                CASE WHEN product.quantityAvailable <= 0 THEN 'This item is no longer available'
                     WHEN item.quantity > product.quantityAvailable THEN 'Not Enough Stock' 
                     WHEN  product.quantityAvailable > item.Quantity THEN REPLACE(note, 'Not Enough Stock', '') 
                     ELSE 'Not Enough Stock' END
            , quantity = 
                CASE WHEN product.quantityAvailable < item.Quantity THEN product.quantityAvailable 
                     ELSE item.Quantity END
        OUTPUT inserted.ID, deleted.note, inserted.note, deleted.quantity, inserted.quantity, 
        INTO @modifiedItems
        FROM item
        INNER JOIN product ON product.ProductID = item.ID

The end goal is for the item table to be updated to reflect the maximum number available across all rows, with the result being:

LineID  |  ProductID  |  Price   |  Quantity  | Note
001     |    00001    |   100    |    2       |
002     |    00001    |    50    |    1       | Not enough stock
003     |    00001    |     0    |    0       | Not enough stock

edit 2: electric boogaloo

I have attempted to use a local variable to calculate a running total, but this seems to jump straight to the total value. Example below:

DECLARE @runningTotalQuantity int = 0
UPDATE item 
        SET 
            note = 
                CASE WHEN product.quantityAvailable <= 0 THEN 'This item is no longer available'
                     WHEN item.quantity > product.quantityAvailable THEN 'Not Enough Stock' 
                     WHEN  product.quantityAvailable > item.Quantity THEN REPLACE(note, 'Not Enough Stock', '') 
                     ELSE 'Not Enough Stock' END
            , quantity = 
                CASE WHEN @runningTotalQuantity != 0 AND @runningTotalQuantity <= ItemLimits.limitedQty AND (@runningTotalQuantity + Item.Quantity) <= ItemLimits.limitedQty then Item.Quantity
                     WHEN (@runningTotalQuantity + Item.quantity) >= ItemLimits.limitedQty THEN (ItemLimits.limitedQty - @runningTotalQuantity) WHEN product.quantityAvailable < item.Quantity THEN product.quantityAvailable 
                     ELSE item.Quantity END
            , @runningTotalQuantity = @runningTotalQuantity + item.Quantity

        OUTPUT inserted.ID, deleted.note, inserted.note, deleted.quantity, inserted.quantity, 
        INTO @modifiedItems
        FROM item
        INNER JOIN product ON product.ProductID = item.ID

But this has the following result:

LineID  |  ProductID  |  Price   |  Quantity  | Note
001     |    00001    |   100    |    2       |
002     |    00001    |    50    |    6       | 
003     |    00001    |     0    |    6       |

It might help others to understand your question if you include some sample data illustrating the problem. — Tim Biegeleisen, Aug 16 '17 at 01:22

score 1 · Answer 1 · answered Aug 17 '17 at 05:40

It is easy to calculate running total in SQL Server 2014, just use SUM() OVER (...). The ordering of results is important for the running total, I used LineID to order the rows. You can choose some other ordering that suits you.

The first CTE in the query below calculates running total for each product and the difference DiffQuantity tells us at which row the product is depleted.

The new value of Quantity is one of three possibilities: 1) If there is still enough stock, the Quantity doesn't change. 2) If there is no stock at all, the Quantity is zero. 3) There can be one row in between where Quantity decreases partially.

Finally, the source table is updated with new values of Quantity and Notes.

Run this query CTE-by-CTE and examine intermediate results to understand how it works.

Sample data

DECLARE @Items TABLE (LineID int PRIMARY KEY, ProductID int, Price money, Quantity int, Note nvarchar(4000));

INSERT INTO @Items (LineID, ProductID, Price, Quantity, Note) VALUES
(001, 00001, 100, 2, ''),
(002, 00001,  50, 2, ''),
(003, 00001,   0, 2, '');

DECLARE @Products TABLE (ProductID int PRIMARY KEY, BasePrice money, QuantityAvailable int);
INSERT INTO @Products (ProductID, BasePrice, QuantityAvailable) VALUES
(00001, 100, 3);

Query

WITH
CTE
AS
(
    SELECT
        I.LineID
        ,I.ProductID
        ,I.Price
        ,I.Quantity
        ,I.Note
        ,P.QuantityAvailable
        ,SUM(I.Quantity) OVER (PARTITION BY I.ProductID ORDER BY I.LineID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQuantity
        ,SUM(I.Quantity) OVER (PARTITION BY I.ProductID ORDER BY I.LineID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        - P.QuantityAvailable AS DiffQuantity
    FROM
        @Items AS I
        INNER JOIN @Products AS P ON P.ProductID = I.ProductID
)
,CTE2
AS
(
    SELECT
        LineID
        ,ProductID
        ,Price
        ,Quantity
        ,Note
        ,QuantityAvailable
        ,DiffQuantity
        ,CASE WHEN DiffQuantity > 0 THEN 'Not enough stock' ELSE '' END AS NewNote

        ,CASE WHEN DiffQuantity > 0
        THEN 
            CASE WHEN Quantity > DiffQuantity
            THEN DiffQuantity
            ELSE 0 END
        ELSE Quantity END AS NewQuantity
    FROM CTE
)
UPDATE CTE2
SET 
    Quantity = NewQuantity
    ,Note = NewNote
;

Result

SELECT * FROM @Items;

+--------+-----------+--------+----------+------------------+
| LineID | ProductID | Price  | Quantity |       Note       |
+--------+-----------+--------+----------+------------------+
|      1 |         1 | 100.00 |        2 |                  |
|      2 |         1 | 50.00  |        1 | Not enough stock |
|      3 |         1 | 0.00   |        0 | Not enough stock |
+--------+-----------+--------+----------+------------------+

score 0 · Answer 2 · answered Aug 16 '17 at 11:31

Have a look at the below query , it may help you

    update item set note = case when precedingSum> t.QuantityAvailable then 'Not Enough Stock'  Else '' End
    from 
    (
    select item.*,Product.QuantityAvailable,sum(Quantity) over(partition by item.ProductId order by item.LineId  rows  UNBounded Preceding)precedingSum
     from Item inner join Product on item.ProductId=Product.ProductId
     ) t where t.LineId = Item.LineId

Insert only up to a maximum number?

2 Answers2