Solved

stored procedure - sum??

Posted on 2003-11-03
9
315 Views
Last Modified: 2008-03-06
I'm trying to do something tricky with an asp.net datagrid.  I have a column that shows $ amounts  for each reference number under a customer number.  Problem is that each customer number has multiple reference numbers and amounts.  I need to total all the amounts up to one number so my datagrid isn't 4x as long as it should be.  This would be very simple if the table I was using included each schedule's beginning amount and ending balance...bleh!

In Crystal Reports this is easily done with a select:  Sum ({Accounting.Amount}), {Accounting.Control#}) > $0.00


So how do I accomplish this with a stored procedure?  It would be nice to write the sum'd amount to a table I use called "Comments".  Any help is appreciated!

0
Comment
Question by:jay-are
  • 5
  • 4
9 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9675661
drop procedure usp_SumByControlNo
go

create procedure usp_SumByControlNo
As
BEGIN
   
   INSERT INTO Comments (ControlNo, SumAmt)
   SELECT ControlNo, Sum(Amout)
    FROM MyTable    
   GROUP BY ControlNo
   HAVING Sum(Amout) > 0

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

RETURN 0

END


HTH
Namasi
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9675844
Do you have this table Comments created already? Does it allow you to insert null values into other columns?
0
 

Author Comment

by:jay-are
ID: 9678823
I do have a sql table called Comments.  It has two columns for now:  Control# & CommentBox.  The actual amounts are in a different table called 'Accounting'.  I was hoping I could just create the column called SumAmt and have the real sums there with the correct control number.

I will test your code in a sec.
thanks!
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:jay-are
ID: 9680532
Namasi:

I got pulled off from this for a second but I did test it out.  Its writing the amounts out to the right table and column but its not matching up the amounts with the control numbers.

The accounting table that contains the control numbers and amounts I'm getting the info has the column names:

'Control#'  'Amount'

The Comments table has these columns:
'ControlNo' 'SumAmt'

So I'm doing all this so I can display the amounts summed up in an asp.net editable datagrid.  I also use the seperate table to store some text comments made in the editable datagrid.  With this stored procedure the way it is now I get the amounts listed, but they are with the wrong control numbers.  Also, there are tons of records listed.  Its showing each entry to the database instead of just giving me one record per control number...

any ideas?
0
 

Author Comment

by:jay-are
ID: 9680534
oh, and the Comments table does allow nulls
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9684072
You may need to add additional conditions on where clause to restrict the result set. Query below will only display distinct control number and the sum for that control number.

SELECT ControlNo, Sum(Amout)
    FROM MyTable    
   GROUP BY ControlNo
   HAVING Sum(Amout) > 0

BTW, 'Control #' does not look like a column name to me.

0
 

Author Comment

by:jay-are
ID: 9689809
Namasi

This is what the procedure looks like now:

CREATE procedure usp_SumByControlNo
As
BEGIN
   
   INSERT INTO Comments (ControlNo, SumAmt)
   SELECT Distinct Control#, Sum(Amount)
   FROM Accounting
   GROUP BY Control#
   HAVING Sum(Amount) > 0
 
   IF @@ERROR <> 0
   BEGIN
     RAISERROR('Insert into Comments failed', 16, 1)
     RETURN -1
   END  

RETURN 0

END
GO

It's not really summing up the amounts for a control number.  The data comes from a schedule that has multiple entries made to control numbers.  So a customer gets charged for something, and then they post a payment for it.  So you have the same amounts going in and out in that data.  ie:  Control# 12345 - $15000
                                                 Control# 12345 - <$15000>

So the datagrid I'm using to display this info is showing ever single sum for all entries made.  I'm trying to get the balance of the Amount for every control#.  The total!  
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 500 total points
ID: 9691074
Could you run this query and post the results

SELECT Control#, Sum(Amount)
   FROM Accounting
   GROUP BY Control#
   HAVING Sum(Amount) > 0

And also post the table definition of Accounting table.

also post results of this query.

select * from Accounting where ControlNo = <Any control number from group by select>
0
 

Author Comment

by:jay-are
ID: 9694431
Namasi:

I modified your query there and ran this instead:

SELECT Control#, Sum(Amount)
   FROM Accounting Where [Acct No] = '1020'
   GROUP BY Control#
   HAVING Sum(Amount) > 0

This gives me exactly what I want!  One control number and the summed amount!  So the problem must be with my datagrid or something, which is a question for a different forum.  Thanks for your help!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

776 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