CODEWITHSUNDEEP
sqlsql-server
YellowLarry
YellowLarry

Reputation: 431

Base on previous record to calculate on a new field

SQL is for Microsoft SQL Server 2014.

The original query result before calculation table is below. It is sorted by Item and OrderDate. The InventoryQty is current available quantity. It is same on each item's records. Will base on first-order-first-serve rule to fulfill order in sequence. Need to calculate a BalanceQty which is available quantity after that record is fulfilled.

Original query records:

Item    OrderDate   OrderQty    InventoryQty    
Item1   2016/11/01  3           10      
Item1   2016/11/03  4           10      
Item1   2016/11/09  2           10      
Item1   2016/11/10  1           10      
Item1   2016/11/11  5           10      
Item1   2016/11/15  6           10      
Item1   2016/11/20  3           10      

Item2   2016/11/11  7           15      
Item2   2016/11/13  4           15      
Item2   2016/11/19  5           15      
Item2   2016/11/20  6           15

Expected Query result with BalanceQty.

Item    OrderDate   OrderQty    InventoryQty    BalanceQty
Item1   2016/11/01  3           10              7
Item1   2016/11/03  4           10              3
Item1   2016/11/09  2           10              1
Item1   2016/11/10  1           10              0
Item1   2016/11/11  5           10              -5
Item1   2016/11/15  6           10              -11
Item1   2016/11/20  3           10              -14

Item2   2016/11/11  7           15              8
Item2   2016/11/13  4           15              4
Item2   2016/11/19  5           15              -1
Item2   2016/11/20  6           15              -7

Upvotes: 1

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You are looking for cumulative sums. In SQL Server 2012+, you would do:

select t.*,
       (InventoryQty - sum(OrderQty) over (partition by item order by OrderDate)) as BalanceQty
from t;

SQL Server 2013 would -- if it existed -- presumably come after 2012 and have this functionality ;) I assume that is a typo.

Upvotes: 5

Related Questions