Solved

SQL Server Running Balance

Posted on 2016-10-06
14
53 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 50

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 50

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

733 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