SQL Server Running Balance
Hello,
I need to update a column in my table called qty_on_hand and have tried using the code below in an update statement, but cannot get it to work. Any ideas would greatly be appreciated.
SELECT Trans_Date, trans_qty,
SUM(trans_qty) OVER(ORDER BY Trans_Date ROWS UNBOUNDED PRECEDING) AS qty_on_hand_running_bal
FROM inventory
GO
I need to update a column in my table called qty_on_hand and have tried using the code below in an update statement, but cannot get it to work. Any ideas would greatly be appreciated.
SELECT Trans_Date, trans_qty,
SUM(trans_qty) OVER(ORDER BY Trans_Date ROWS UNBOUNDED PRECEDING) AS qty_on_hand_running_bal
FROM inventory
GO
Scott Pletcher
"SUM OVER" was not available in SQL 2005, if that is the SQL version you are using.
ASKER
Scott,
I did not see a topic area for 2012, but that is what I am using for this one.
I did not see a topic area for 2012, but that is what I am using for this one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
May be this..if uniqueness is an issue. .not tested.
--
;WITH CTE1 AS
(
SELECT Trans_Date, trans_qty,
ROW_NUMBER() OVER (ORDER BY Trans_Date ASC ROWS UNBOUNDED PRECEDING) rno
FROM dbo.inventory
)
,CTE2 AS
(
SELECT Trans_Date, trans_qty,
SUM(trans_qty) OVER(ORDER BY rno ASC) runningBalanceQuantity
FROM CTE1
)
UPDATE CTE2
SET CTE2.trans_qty = CTE2.runningBalanceQuantity
--
Scott's solution should work if like he said, the Trans_Date is unique. If not, please provide the table schema.
ASKER
Scott,
Wanted to let you know that it worked! The only thing I added to the code was a partition statement to segment the running totals unique to each id (inventory item). Many thanks.
Wanted to let you know that it worked! The only thing I added to the code was a partition statement to segment the running totals unique to each id (inventory item). Many thanks.
ASKER
Vitor,
Many thanks. I ran the statement, but got the error of
"The function 'ROW_NUMBER' may not have a window frame."
I don't think I have seen that one before, but it may have to do with order by? I tried to fix but kept getting the same error.
Many thanks. I ran the statement, but got the error of
"The function 'ROW_NUMBER' may not have a window frame."
I don't think I have seen that one before, but it may have to do with order by? I tried to fix but kept getting the same error.
Can you post you query ?
H-SC, I didn't provide any query. Just pointed you to Scott's solution that seems to be want you're looking for.
Maybe you're referring to Pawan's solution? He used the ROW_NUMBER function.
Maybe you're referring to Pawan's solution? He used the ROW_NUMBER function.
ASKER
Correction on my last post....
was for Pawan Kumar Khowal and not Vitor
Pawan Kumar Khowal,
Many thanks. I ran the statement, but got the error of
"The function 'ROW_NUMBER' may not have a window frame."
I don't think I have seen that one before, but it may have to do with order by? I tried to fix but kept getting the same error.
was for Pawan Kumar Khowal and not Vitor
Pawan Kumar Khowal,
Many thanks. I ran the statement, but got the error of
"The function 'ROW_NUMBER' may not have a window frame."
I don't think I have seen that one before, but it may have to do with order by? I tried to fix but kept getting the same error.
Updated code...
;WITH CTE1 AS
(
SELECT Trans_Date, trans_qty,
ROW_NUMBER() OVER (ORDER BY Trans_Date ASC) rno
FROM dbo.inventory
)
,CTE2 AS
(
SELECT Trans_Date, trans_qty,
SUM(trans_qty) OVER(ORDER BY rno ASC) runningBalanceQuantity
FROM CTE1
)
UPDATE CTE2
SET CTE2.trans_qty = CTE2.runningBalanceQuantit
;WITH CTE1 AS
(
SELECT Trans_Date, trans_qty,
ROW_NUMBER() OVER (ORDER BY Trans_Date ASC) rno
FROM dbo.inventory
)
,CTE2 AS
(
SELECT Trans_Date, trans_qty,
SUM(trans_qty) OVER(ORDER BY rno ASC) runningBalanceQuantity
FROM CTE1
)
UPDATE CTE2
SET CTE2.trans_qty = CTE2.runningBalanceQuantit
ASKER
Pawan,
Many thanks, again that works!
Many thanks, again that works!
Welcome !!