SQL Update column as running balance

Posted on 2014-03-12
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?
Question by:lburnsinmagnolia
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
  • 2
  • 2
LVL 143

Accepted Solution

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
   SET InventoryCostBalance  = ( SELECT SUM(h,OrigInventoryCost)
                   FROM TRANSACTIONS h
                   WHERE h.AgreementID  = t.AgreementID    
                       AND h.TRANSACTIONDATE <= t.TRANSACTIONDATE  

Open in new window


Author Comment

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.
LVL 143

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?

Author Comment

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


Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor ( Top Charts is a view in which you can set seve…

632 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