Advertisements
I try to sum value in pervious rows, I want to sum the pervious quantity "Stock on hand" and put the result in each row, example
| ItemID | Qty | Stockon Hand ( the result updated in this column) |
|---|---|---|
| 1000 | 1 | 1 |
| 1000 | 5 | 6 ( sum qty previous in pervious row plus the qty in the current row) |
| 1000 | 2 | 8 ( sum qty previous in pervious row plus the qty in the current row) |
| 1000 | 1 | 9 ( sum qty previous in pervious row plus the qty in the current row) |
How can I update the column "Stock on hand" by summing the qty in the current row and previous rows?
select ItemID, Qty
, sum(qty) over (order by itemid rows between 1 preceding and 1 preceding) as previous_Qty
from #Stock
order by itemid
>Solution :
Here is a working example. Note, I took the liberty of adding an column for a proper sequence… ID, but you could use a date column as well
You may also notice that I added partition by ItemID
Declare @YourTable Table ([ID] int,[ItemID] int,[Qty] int)
Insert Into @YourTable Values
(1,1000,1)
,(2,1000,5)
,(3,1000,2)
,(4,1000,1)
Select *
,OnHand = sum(Qty) over (partition by ItemID order by ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from @YourTable
Results
ID ItemID Qty OnHand
1 1000 1 1
2 1000 5 6
3 1000 2 8
4 1000 1 9