[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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.
0
Break40
Asked:
Break40
  • 3
  • 2
3 Solutions
 
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
 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now