CODEWITHSUNDEEP
sqlsql-serverdatabase
Jeff McClain
Jeff McClain

Reputation: 63

Include Value From Previous Row

I'm using SQL Server here. I have a table for inventory where only January has a beginning quantity. Each row has a changed quantity for that month.

So..

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

So in the above case, January would be 10, February 9, and March 13, and so on...

Is it possible to take care of this line by line in SQL?

I looked into lag briefly but I'm not so sure it does what I need or if it's the best way to go about this.

Upvotes: 4

Views: 49

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

You were on the right track with the window functions, however, Lag() would not be much help here. The good news is 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

Upvotes: 5

Related Questions