How to check for available stocks and highlight them in an order using a view in SQL Server

Question

I have two tables: stock and order:

warehouse:

*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 look:

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 the "Stockleft" column. I would like to calculate the stock remaining after I subtracted the number of items in the index.

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 a cumulative amount, and then subtract it from the initial amount. This is join and the 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 amounts are supported in SQL Server 2012+. In earlier versions, you can do something similar with apply or a correlated subquery.