Tony Tony - 6 months ago 10
SQL Question

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

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

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.

Comments