Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-05-13
6
661 Views
Last Modified: 2012-05-06
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
Comment
Question by:Bruce
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24376553
Try the following:
((3 * 100) /  9.0)

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24376591
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
 
LVL 22

Accepted Solution

by:
8080_Diver earned 300 total points
ID: 24376827
@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
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.

 
LVL 1

Author Comment

by:Bruce
ID: 24377266
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 24377368
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

Open in new window

0
 
LVL 1

Author Closing Comment

by:Bruce
ID: 31581054
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

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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