Decrement quantity from result set in SQL

Question

It feels like a very simple question but I cannot find the right solution, so maybe I'm asking the wrong question.

I have a simple result set. Lists the same part N times. It requires N parts for each record. It also has a total quantity on hand of N.

Id | Part | QtyRequired | OnHandQty
1  | 123  | 10          | 100
2  | 123  | 5           | 100
3  | 123  | 22          | 100

etc..

How could I return the "Remaining" quantity for a result set like this..

Id | Part | QtyRequired | OnHandQty | RemainingQty
1  | 123  | 10          | 100       | 90
2  | 123  | 5           | 100       | 85
3  | 123  | 22          | 100       | 63

Answer 1

You can sum the quantity required per part entry:

Working Example:

DECLARE @Inventory TABLE
(
    Id int,
    Part int,
    QtyRequired INT,
    OnHandQty INT
)

INSERT INTO @Inventory (Id, Part, QtyRequired, OnHandQty)
    VALUES (1, 123, 10, 100),
            (2, 123, 5, 100),
            (3, 123, 22, 100)


SELECT
    Id
   ,Part
   ,QtyRequired
   ,OnHandQty
   ,OnHandQty - SUM (QtyRequired) OVER (PARTITION BY Part ORDER BY Id) AS RemainingQty
FROM @Inventory

If you need to know the quantity used, you can include the following:

SUM (QtyRequired) OVER (PARTITION BY Part ORDER BY Id) AS QuantityUsed