Solved

SQL Server Running Balance

Posted on 2016-10-06
14
36 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:ScottPletcher
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:
ScottPletcher 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
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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
Backup Your Microsoft Windows Server®

Backup 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.

 
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 18

Expert Comment

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

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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41833756
Welcome !!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now