Search code examples
sql-serversql-server-2016

SQL Server 2016 - Calculating remaining stock quantity

I have a table like this:

Table Layout

As you can see, I have a column called remaining_stock that is correct for the first line of each item but NOT for the subsequent lines. The first line is straight forward as you can simply subtract the order-quantity from initial_stock..

What I want to achieve is to get a remaining_stock column that looks like this:

Desired Result

I though of using row_number and then joining back to the same table using the row numbers.. but that doesn't quite work either. can someone point me in the right direction please?

select 1 as line, 123 as item, 5 as order_quantity,10 as intial_stock
union all
select 2 as line, 123 as item, 3 as order_quantity,10 as intial_stock
union all
select 3 as line, 123 as item, 1 as order_quantity,10 as intial_stock
union all
select 4 as line, 234 as item, 5 as order_quantity,15 as intial_stock
union all
select 5 as line, 234 as item, 3 as order_quantity,15 as intial_stock
union all
select 6 as line, 234 as item, 1 as order_quantity,15 as intial_stock
Solution

  • Small matter using the window function Sum() over

    Example

    Select * 
      ,Remaining_Stock = intial_stock - sum(Order_Quantity) over (Partition By Item Order by Line)
 from YourTable

    Returns

    line    item    order_quantity  intial_stock    Remaining_Stock
1       123     5               10              5
2       123     3               10              2
3       123     1               10              1
4       234     5               15              10
5       234     3               15              7
6       234     1               15              6