asked on

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.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Pickart

ASKER

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

David Pickart

ASKER

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.

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial