Include value from previous line

Question

I am using SQL Server here. I have an inventory table where only January has an initial quantity. Each row has a changed quantity for this month.

So..

Item | Month | Beg | PerChg
---------------------------
001    1       5      5
001    2       0     -1
001    3       0      4

So, in the above case, January will be February 10, 9 and March 13, etc ...

Can I take care of this line by line in SQL?

I looked into the lag a bit, but I'm not sure if he is doing what I need, or if this is the best way to do it.

Answer 1

You were on the right track with window functions , however Lag () wouldn't help here. The good news is that you can use sum () over

Example

Declare @YourTable Table ([Item] varchar(50),[Month] int,[Beg] int,[PerChg] int)
Insert Into @YourTable Values 
 ('001',1,5,5)
,('001',2,0,-1)
,('001',3,0,4)

Select * 
      ,RunningTotal = sum(Beg+PerChg) over (Partition By Item Order by Month)
 From @YourTable

Returns

Item    Month   Beg     PerChg  RunningTotal
001     1       5       5       10
001     2       0       -1      9
001     3       0       4       13