SQL Server - Calculate current row value using pre

问题:

I have following table and using SQL Server 2008

    Tbl_TRNSACTION

 RowNum    Transaction Type        InQty     OutQty     InPrice
    1              IN               20          0           20
    2              IN               50          0           40
    3              OUT              0           10          - 
    4              IN               10          0           30

I expect output in following format

If TransType is IN,

then (InQty*InPrice) is added in CumulativeStockVal

and StockRate = (CumulativeStockVal / Balance).

If Transaction Type is OUT,

then (OutQty*previous(StockRate)) is subtracted from CumulativeStockVal.

RowNum   TransType InQty   OutQty  Balance  CumulatveStockVal  StockRate

  1        IN      20       0       20      400                20
  2        IN      50       0       70      2400               34.285
  3        OUT     0        10      60      2057.15            34.285
  4        IN      10       0       70      2357.15            33.673

Please reply.Thanks.

回答1:

Try using Window Offset Functions combining with AGGREGATE functions.

EDIT: I found you a tut where it is shown very clearly, watch from minute 15.

https://www.microsoftvirtualacademy.com/en-US/training-courses/querying-microsoft-sql-server-2012-databases-jump-start-8241?l=OZmttuJy_2304984382