Solved

Stored Procedure quirks - Help!

Posted on 2004-04-30
6
218 Views
Last Modified: 2006-11-17
I have problems with an existing stored procedure that just recently cropped up with a server change.  This is the sproc:

CREATE procedure usp_Comments1130
As
BEGIN
   
   
   INSERT INTO Comments1130 (ControlNo, SumAmt)
   SELECT  [Control #], Sum(Amount)
   FROM ODS_accounting_Schedule4 Left Join Comments1130 on ODS_accounting_Schedule4.[Control #] = Comments1130.ControlNo Where ods_accounting_schedule4.[Account #] = '1130' AND Comments1130.ControlNo Is Null
   GROUP BY [Control #]
   HAVING Sum(Amount) <> 0

   IF @@ERROR <> 0
   BEGIN
     RAISERROR('Insert into Comments failed', 16, 1)
     RETURN -1
   END  

 
   DELETE FROM Comments1130
   WHERE 0 = (
        SELECT SUM(Sched4.Amount)
        FROM ODS_accounting_Schedule4 Sched4
        WHERE Sched4.[Control #] = Comments1130.ControlNo
        AND Sched4.[Account #] = '1130'
   )


RETURN 0

END
GO

This seemed to be working fine (no reported errors before now) before a recent server change.  Now it doesn't actually give me the Sum(Amount) properly.  I'll post the data here for the specific control #'s that its fouling up:

Ctrl#         Acct#        Amount    Reference#
24122      1130      -50.0000      013004
24122      1130      -25.0000      032504
24122      1130      -25.0000      041504
24122      1130      500.0000      8470
25819      1130      -1250.0000      0425CASH
25819      1130      -800.0000      022404
25819      1130      -500.0000      032404
25819      1130      2500.0000      1055
26755      1130      250.0000      1073
26755      1130      500.0000      1084

There are only 4 control #'s in this table that have multiple entries.  The three above do not actually get sum'd up by the sproc correctly.  The only one that does is this one:

454593      1130      -150.0000      0216CASH
454593      1130      1400.0000      8470

I don't understand why it would work for '454593' and not the rest.  I need help cleaning this up because my reports based off this are now inaccurate!  Help!

Thanks.

0
Comment
Question by:jay-are
  • 3
  • 3
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10963189
None of the control #s:

24122  
25819
26755

already exist in table Comments1130, right?  Because if they already the existing SUM() will naturally not be replaced.
0
 

Author Comment

by:jay-are
ID: 10963203
Yes they are in that table.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 10963282
Hmm, maybe I don't really understand the problem :-)


Is the problem that the SUM in the Comments1130 table is incorrect?

If so, then did the last run of the code you gave produce the incorrect sum, placing the wrong amount in Comments1130
<or>
was there a change(s) to an existing control # and the *new* sum is not reflected in Comments1130 (but the old sum was correct)?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:jay-are
ID: 10963317
You pointed out the problem I didn't know I was having.  I need it to update each control # when  a new line/amount is added in the sched4 table.  :)

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 10963563
Maybe something like this? :-) :

UPDATE Comments1130
SET SumAmt = sched4.SumAmount
FROM Comments1130
INNER JOIN (
    SELECT  [Control #], Sum(Amount) AS SumAmount
    FROM ODS_accounting_Schedule4
    WHERE [Account #] = '1130'
    GROUP BY [Control #]
) AS sched4 ON Sched.[Control #] = Comments1130.ControlNo
WHERE Comments1130.SumAmt <> sched4.SumAmount


NOTE:
It should be slightly more efficienct to run this before the INSERT.
0
 

Author Comment

by:jay-are
ID: 10963676
I feel stupid for this but this works perfectly.  Can't believe I even had to ask why it wasn't working...  :)  Thanks!

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now