Solved

SQL Update column as running balance

Posted on 2014-03-12
4
403 Views
Last Modified: 2014-03-12
I have a table - TRANSACTIONS that has columns AGREEMENTID, TRANSACTIONDATE, OrigInventoryCost, InventoryCostBalance.  I need the InventoryCostBalance updated to be a running total of OrigInventoryCost.  Right Now, the InventoryCostBalance is all zeroes.

Here is a small sampling of what it should look like.

AgreementID    TRANSACTIONDATE  OrigInventoryCost   InventoryCostBalance (should be)
1                         07/09/13 08:00:00                     299.99                     299.99
1                         07/09/13 08:01:00                      -25.00                     274.99
1                         08/09/13 08:00:00                      -25.00                     249.99

I cannot use a CURSOR as there are upwards of 2 million records now and many added daily.   I can handle the daily additions, but cannot seem to get the table updated with current records.

Does anyone know how to get the data updated properly?
0
Comment
Question by:lburnsinmagnolia
  • 2
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39923482
step 1: ensure you have a (clustered) index on AgtreementID + TransactionDate

step 2: run this code
UPDATE t
   SET InventoryCostBalance  = ( SELECT SUM(h,OrigInventoryCost)
                   FROM TRANSACTIONS h
                   WHERE h.AgreementID  = t.AgreementID    
                       AND h.TRANSACTIONDATE <= t.TRANSACTIONDATE  
          )
  FROM TRANSACTIONS  t 

Open in new window

0
 

Author Comment

by:lburnsinmagnolia
ID: 39923918
I have tried that, the problem is that since it's a running total, the only row that gets updated is the very next row.  All subsequent rows have the same InventoryCostBalance as the second row.  So in my sample above, the 3rd row is set to 274.99 and if there are more rows, they too would be 274.99

Since the Primary key is clustered (identity column), I cannot create a clustered index.  I have a nonclustered one on the agreementid, transactiondate.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923954
I admit I have a typo in my above code, inside the SUM() function it should be "." and not ",".

apart from that, the code works correctly, tested:
declare @transactions table ( AgreementID    int , TRANSACTIONDATE  datetime, OrigInventoryCost  decimal(10,2), InventoryCostBalance decimal(10,2))
insert into @transactions ( AgreementID    , TRANSACTIONDATE, OrigInventoryCost)
select 1, getdate()-1, 299.99
insert into @transactions ( AgreementID    , TRANSACTIONDATE, OrigInventoryCost)
select 1, dateadd(minute, 1, getdate()-1 ), -25
insert into @transactions ( AgreementID    , TRANSACTIONDATE, OrigInventoryCost)
select 1, dateadd(minute, 10, getdate()-1 ), -25

update t  
SET InventoryCostBalance  = ( SELECT SUM(h.OrigInventoryCost)
                   FROM    @transactions  h
                   WHERE h.AgreementID  = t.AgreementID    
                       AND h.TRANSACTIONDATE <= t.TRANSACTIONDATE  
          )
  from @transactions t

  select * from @transactions 

Open in new window

output is:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(3 row(s) affected)

AgreementID TRANSACTIONDATE         OrigInventoryCost                       InventoryCostBalance
----------- ----------------------- --------------------------------------- ---------------------------------------
1           2014-03-11 17:00:49.533 299.99                                  299.99
1           2014-03-11 17:01:49.533 -25.00                                  274.99
1           2014-03-11 17:10:49.533 -25.00                                  249.99

(3 row(s) affected)

Open in new window


if it does "not work" for you, the first guess I would make is that the transaction_date field is not datetime, but varchar?
0
 

Author Comment

by:lburnsinmagnolia
ID: 39924082
That worked!  It works without an index, but takes longer.  I will correct that.

Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

14 Experts available now in Live!

Get 1:1 Help Now