SQL Update column as running balance

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?
lburnsinmagnoliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lburnsinmagnoliaAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
lburnsinmagnoliaAuthor Commented:
That worked!  It works without an index, but takes longer.  I will correct that.

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.