SQL-2005 Stored Procedure to check running total

I need to create a stored procedure to check every record in a table sorted by SalesRepNo and SalesID.  There is a field called "SalesAmt" that may be positive or negative value and another field called "RunBalance" that holds a running total.  The records are sorted by SalesRepNo, SalesID. I want a stored procdure to loop through all records and verify that the running balance is correct.  If it's not I need to fix and send record to a log.

table: salesTrans
fields:salesRepNo, SalesID, SalesAmt, RunBalalnce

I am looking for a starting point on how to do this.  What the stored proc should look like.
Break40Asked:
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.

Scott PletcherSenior DBACommented:
How large is the table?

Is there an index(es) on SalesRepNo and/or SalesID?
0
Scott PletcherSenior DBACommented:
Here is a sample approach; run time will depend on size of tables and index(es) available.


IF OBJECT_ID('tempdb.dbo.#newRunBalances') IS NOT NULL
    DROP TABLE #newRunBalances
SELECT *
INTO #newRunBalances
FROM (
    SELECT salesRepNo, SalesID,
        (SELECT SUM(SalesAmt)
        FROM salesTrans st2
        WHERE st2.salesRepNo = st1.salesRepNo
        AND st2.SalesID <= st1.SalesID) AS NewRunBalance
    FROM salesTrans st1
    WHERE RunBalance <>
        (SELECT SUM(SalesAmt)
        FROM salesTrans st2
        WHERE st2.salesRepNo = st1.salesRepNo
        AND st2.SalesID <= st1.SalesID)
) AS newRunBalances


UPDATE salesTrans
SET RunBalance = NewRunBalance
FROM salesTrans
INNER JOIN newRunBalances ON
    newRunBalances.salesRepNo = salesTrans.salesRepNo AND
    newRunBalances.SalesID = salesTrans.SalesID

--do desired logging using # temp table

DROP TABLE #newRunBalances

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
Break40Author Commented:
In a nutshell the table I am working with continually has records added tracking transactions.  As the new record is added the "runBalance"  gets adjusted based on sales amount.  Sometimes this fails and the balance is not correct.  What I am looking to do is create the code in a stored procedure to loop through all records in a table and adjust the runbalance if needed.  For example. The table would look like below.

recNo  salesRepNo  SalesAmt  RunBalance  
1         200                1000          1000
2         200                5000          6000
3         200                -2000         4000

0
Break40Author Commented:
The table has 208,000 rows.  I am trying your sugestions and it works on small test table, but against the larger one it runs forever.
0
Scott PletcherSenior DBACommented:
Create an index on:

(salesRepNo, SalesID, SalesAmt)
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
Query Syntax

From novice to tech pro — start learning today.