How to check for available stock and allocate them to an order using a view in SQL Server

Question

I have two tables: stock and orderrow:

stock:

*id |  name  |   stockcount
----------------------------
1  | item1  |  10
2  | item2  |  9

Orderrow:

id   |     date   |  itemid  | ordercount
------------------------------------------
1001 |  1/1/2016  |     1    |   5
1002 |  1/1/2016  |     2    |   8
1003 |  1/1/2016  |     2    |   1 
1004 |  1/1/2016  |     1    |   3 
1005 |  1/1/2016  |     1    |   5

I would like to have the following view:

qry_orderrow:

id   |     date   | itemid |  ordercount |  stockleft
------------------------------------------------------
1001 |  1/1/2016  |   1    |    5        |   5
1002 |  1/1/2016  |   2    |    8        |   1
1003 |  1/1/2016  |   2    |    1        |   0
1004 |  1/1/2016  |   1    |    3        |   2
1005 |  1/1/2016  |   1    |    5        |   -3

As you can see I added a column 'stockleft'. I would like to calculate the stock left after I subtracted the number of items of the orderrow.

Does anyone know how to create this query using SQL Server? I really don't know how to do this, so any help would be greatly appreciated!

Answer 1

You need the cumulative amount ordered and then to subtract that from the initial amount. This is a join and a fancy window function:

select o.*,
       (s.stockcount -
        sum(o.ordercount) over (partition by itemid order by date)
       ) as stockleft
from orderrow o join
     stock s
     on o.itemid = s.itemid;

Cumulative sums are supported in SQL Server 2012+. In earlier versions, you can do something similar with apply or a correlated subquery.