Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Running Balance

Posted on 2016-10-06
14
Medium Priority
?
65 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
14 Comments
 
LVL 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

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 52

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 32

Expert Comment

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

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 32

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 32

Expert Comment

by:Pawan Kumar
ID: 41833756
Welcome !!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

610 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