x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 674

# Why is my SQL SELECT calculation completing the decimal places to the right?

I am trying to get a table-valued function to return a percentage.

I've tried both a numeric type and a decimal with different results.

Boiled down this is my calculation:

DECLARE @CompletionPercent NUMERIC(18,8)
--DECLARE @CompletionPercent DECIMAL
Set @CompletionPercent =
(
(
(3*100000000)
/  9
)
)
SELECT  @CompletionPercent / 1000000

The result is this:
33.3333330000000000

Notice the zeros at the end.  I'd like to get the calculation to be carried out farther.

Thanks!
0
Bruce
• 2
• 2
• 2
2 Solutions

Commented:
Try the following:
``````((3 * 100) /  9.0)
``````
0

IF you want to ROUND the result use ROUND function
eg :  SELECT ROUND( 33.333333, 2)
If you just need to return 2 decimal points
SELECT CAST ( 33.3333 as Numeric (5,2)  )
0

Commented:
@aneeshattingal,
Actually, he is trying to get more decimal places of accuracy.
@cylikon,
The problem is that you are working with integer arithmetic and you aer getting only as many deciaml places as the number of zeros behind the "3" that you are dividing by 9.
You first multiple the 3 by 100000000 (which has 8 zeros following the 1), then you divide by 9, which gives you 33333333 (notice, 8 3's).  That valu is what your @CompletionPercent is set to.
Then you divide the @CompletionPercent by 1000000 (notice, 6 zeros) which gives you the 33 and six 3's following it.
What you probably should do is to multiply your 3 (or whatever value) by 100 and then divide by 9.0, which will make cause the calculation to not be done as integer arithmenic and that will give you the calculated percentage in the format you are trying to achieve.
0

Author Commented:
Good stuff, thanks.  I have the calculation working in a query window but my scalar-valued function is still not returning the right value.  Here's what I setup to test in the query window.

DECLARE @denom AS DECIMAL, @numerator AS INT
SET @denom = 9
SET @numerator = 3
select (
(@numerator * 100)
/  @denom
)

This returns exactly what I am looking for: 33.3333333333333333333

But I must have missed something in the translation to the SVF.  Here's that code:

ALTER FUNCTION [Governance].[fnCompletionPercent]
(
@EventID INT
)
RETURNS DECIMAL
AS
BEGIN
DECLARE @CompletionPercent DECIMAL
DECLARE @denom DECIMAL

SELECT @denom = fnMyTotalCount(@EventID)

IF @denom > 0
BEGIN
SET @CompletionPercent =
(
(
(fnCompletedCount(@EventID) * 100)
/  @denom
)
)
END
ELSE
SET @CompletionPercent = 0.0

RETURN @CompletionPercent
END

This function returns the integer 4 when my numerator is 1 (then * 100) and the denominator is 24.

1*100/24

Where am I going wrong?
0

change the fn definition like this
``````ALTER FUNCTION [fnCompletionPercent]
(
@EventID INT
)
RETURNS DECIMAL (5,2)
AS
BEGIN
DECLARE @CompletionPercent DECIMAL(5,2)
DECLARE @denom decimal
``````
0

Author Commented:
Great!!!  :-))
Thanks aneeshattingal for the quick response.
Thanks 8080_Diver for digging deeper and educating me.

If I could give you both 1000 points I would.  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.