Trouble getting accurate calculation with UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trouble getting accurate calculation with UDF

Author

Topic

The_Utimate_Novice
Starting Member

7 Posts

Posted - 2011-11-13 : 15:33:40

Hello everyone! I am trying to perform calculation using a User Define Function and a CustomerID as the Input Parameter.
The calculation seem to work if I am performing calculating a single row.

However, because a single customer can make many support calls, a customer can have multiple row of charges.

If I try to calculate using CustomerID 1, the amount I get is not accurate. I have turned this code upside down and can't understand how to get this going.

Here is a snap shot of the Support Charges.

And here is the function:

CREATE FUNCTION dbo.CalulateTotal (@CustomerId INT) 
RETURNS MONEY 
AS 
BEGIN
DECLARE @GetTotalCost MONEY         
SELECT  @GetTotalCost = ISNULL((SUM(SupportHours) * MAX(CostPerHour)),0) + ISNULL((SUM(MilesTraveled) * MAX(CostPer_Mile)),0)
+ ISNULL((SUM(Supplies) + MAX(Misc)),0)
FROM tblSupportCharges                 
WHERE CustomerId = @CustomerId
GROUP BY CustomerId

RETURN @GetTotalCost
END

Select dbo.CalulateTotal(1)

The result for calculating CustomerId 1 SHOULD BE 13 NOT 15.

Any help will be greatly appreciated.

TUN

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-13 : 16:45:10

Should it be supplies + max(misc)?
Also should the isnull be round the combination?
Maybe something like this?

sum(ISNULL(SupportHours * CostPerHour,0) + ISNULL(MilesTraveled * CostPer_Mile,0) + ISNULL(Supplies,0) + ISNUKK(Misc,0))

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The_Utimate_Novice
Starting Member

7 Posts

Posted - 2011-11-13 : 17:05:16

quote:
Originally posted by nigelrivett

Should it be supplies + max(misc)?
Also should the isnull be round the combination?
Maybe something like this?

sum(ISNULL(SupportHours * CostPerHour,0) + ISNULL(MilesTraveled * CostPer_Mile,0) + ISNULL(Supplies,0) + ISNUKK(Misc,0))

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yes you are right, I played around with the expression and changed it to this, similar to what you had suggested and it works!

CREATE FUNCTION dbo.CalulateTotal (@CustomerId INT) 
RETURNS MONEY 
AS 
BEGIN
DECLARE @GetTotalCost MONEY 
SELECT  @GetTotalCost = ISNULL(SUM(SupportHours * CostPerHour),0) + ISNULL((SUM(MilesTraveled * CostPer_Mile)),0)+ ISNULL(SUM(Supplies),0) + ISNULL(SUM(Misc),0)
FROM tblSupportCharges                 
WHERE CustomerId = @CustomerId
GROUP BY CustomerId

RETURN @GetTotalCost
END

Thanks a million!

TUN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources