Solved

SQL Server Running Balance

Posted on 2016-10-06
14
45 Views
Last Modified: 2016-10-07
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
Comment
Question by:H-SC
  • 5
  • 4
  • 2
  • +1
14 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41832592
"SUM OVER" was not available in SQL 2005, if that is the SQL version you are using.
0
 
LVL 1

Author Comment

by:H-SC
ID: 41832597
Scott,
I did not see a topic area for 2012, but that is what I am using for this one.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41832621
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41832826
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41833181
Scott's solution should work if like he said, the Trans_Date is unique. If not, please provide the table schema.
0
 
LVL 1

Author Comment

by:H-SC
ID: 41833698
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
 
LVL 1

Author Comment

by:H-SC
ID: 41833710
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833714
Can you post you query ?
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41833719
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
 
LVL 1

Author Comment

by:H-SC
ID: 41833723
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833730
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
 
LVL 1

Author Comment

by:H-SC
ID: 41833755
Pawan,
Many thanks, again that works!
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41833756
Welcome !!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 31
Need a SQL query that creates a header row and one or more detail rows. 7 30
create an aggregate function 9 31
VB.net and sql server 4 33
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question