Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
H-SC
Asked:
H-SC
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Scott PletcherSenior DBACommented:
"SUM OVER" was not available in SQL 2005, if that is the SQL version you are using.
0
 
H-SCAuthor Commented:
Scott,
I did not see a topic area for 2012, but that is what I am using for this one.
0
 
Scott PletcherSenior DBACommented:
Even if Trans_Date includes a time, it's not guaranteed to be unique.  [Or is it?  Do you have a unique constraint on Trans_Date?  Otherwise I think SQL will need a more unique key??]

Maybe try this if you want to UPDATE, not sure if it will work or not:

;WITH cte_running_totals AS (
    SELECT Trans_Date, trans_qty,
        SUM(trans_qty) OVER(ORDER BY Trans_Date ROWS UNBOUNDED PRECEDING) AS qty_on_hand_running_bal
    FROM dbo.inventory
)
UPDATE cte_running_totals
SET qty_on_hand = qty_on_hand_running_bal
1
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Pawan KumarDatabase ExpertCommented:
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

--

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Scott's solution should work if like he said, the Trans_Date is unique. If not, please provide the table schema.
0
 
H-SCAuthor Commented:
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.
0
 
H-SCAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
Can you post you query ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
1
 
H-SCAuthor Commented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
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.runningBalanceQuantity
1
 
H-SCAuthor Commented:
Pawan,
Many thanks, again that works!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome !!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now