Extra decimal places when using SUM

[AccessUser22]

I'm completely at a loss as to what is happening in my views. I have a subquery that has values 255.42 and 319.275 for one field and one employee in my query. If I run a SUM on top of this query on this field, for some reason it gives me a bunch of extra decimal points. The answer should be 574.695, but instead it's giving me 574.69499206543. Where are all these extra decimal points coming from.

My main issue is I need to round this to two decimal places, which should be 574.70, but instead because of the extra decimals I'm getting 574.69.

Where am I going wrong?

 

[SQLDenis]

here is a dirty kludge


select round(round(574.69499206543,3),2)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

The problem here is that you are not being careful with your data types. Somewhere, somehow, a float is getting included in to the calculations. Take a look at this example.

Declare @Temp Table(Id Int, Value Decimal(10,3))

Insert Into @Temp Values(1, 255.42)
Insert Into @Temp Values(2,319.275)

Declare @Value Float
Set @Value = 0

Select Sum(Value) + @Value As FloatAddedIn, 
       Sum(Value)          As WithoutFloat
From   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

I don't get this problem

select round(sum(x),2) from(
select 255.42  x union all
select 319.2750) z

what is your data type? evil floats?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[AccessUser22]

I kind of figured it has something to do with data type. I'll have to look tomorrow to see where a float could be coming in. I went back out to my table and any reals I got rid of. Changed them to either money or decimal, but the problem is still there. Any calculates I have done to get the 255.42 and 319.275 I've made sure to cast as decimal. So I have no idea why those decimals are popping up. I'll take a closer look in the morning. Thanks.

 

[gmmastros]

While you are at it, take a look for hard coded numbers too, because they can sometimes cause unexpected results.

Declare @A Decimal(10,2)

Set @A = 255.42

Select @A, @A [!]* 1.0[/!], @A [!]/ 1.0[/!]

Results:

[tt][blue]

------------ --------------- -----------------
255.42 255.42[!]0[/!] 255.42[!]0000[/!]

(1 row(s) affected)
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AccessUser22]

Question - If you are using smalldatetime to compute total hours, what is the resulting datatype when using DateDiff?

I use this at one point in one of my sub-views:
CAST((CEILING(DATEDIFF(Minute, MinArrivalTime, MaxDepartTime) / 60.0 * 2.0) / 2.0) AS decimal(18, 1))

Wouldn't this format the value as a decimal going forward?

 

[AccessUser22]

I do have some hard coded numbers. In some of my CASE statements I need to check to see if a value is under 8. If so, it becomes 8 otherwise it stays the same number.

Ex:
CASE WHEN TotalDayHours <= 8 THEN 0 ELSE TotalDayHours - 8 END

What would be a better way to code this to avoid the 8 being taken in as a float?

 

[gmmastros]

The 8 here is not causing your problem. Initially, 8 will be interpretted as as integer. When you use it in a math equation, it will be 'promoted' to another data type that matches the data type of TotalDayHours.

Ex:

Declare @TotalDayHours Decimal(10,1)
Set @TotalDayHours = 17.5

Select @TotalDayHours - 8

Take a look here for SQL Server's data type precedence.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ESquared]

Perhaps it's appropriate here to share a VarType function I wrote. I don't use it very often, but when I do it saves me a lot of time and hassle.

CREATE FUNCTION VarType(@Variable sql_variant)
RETURNS varchar(256)
AS
BEGIN
   DECLARE @VarType varchar(256)
   SET @VarType = Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType'))
   IF @VarType LIKE '%char%' OR @VarType LIKE '%binary%' BEGIN
      SET @VarType = @VarType + '(' + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'MaxLength')) + ')'
   END
   ELSE IF @VarType IN ('decimal', 'money', 'numeric') BEGIN
      SET @VarType = @VarType + '('
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Precision'))
         + ','
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Scale'))
         + ')'
   END
   RETURN @VarType
END

It can also be written as a single statement, but it's kind of ugly:

CREATE FUNCTION VarType(@Variable sql_variant)
RETURNS varchar(256)
AS
BEGIN
   RETURN
      Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType'))
      + CASE
      WHEN Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) LIKE '%char%' OR Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) LIKE '%binary%'
         THEN '(' + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'MaxLength')) + ')'
      WHEN Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'BaseType')) IN ('decimal', 'money', 'numeric') 
         THEN '('
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Precision'))
         + ','
         + Convert(varchar(256), SQL_VARIANT_PROPERTY(@Variable, 'Scale'))
         + ')'
      ELSE ''
      END
END

I have no idea which performs better.

 

[AccessUser22]

Okay, I think I've got it. I went through all my sub queries and made sure when I did a computation that the returning value was a decimal. The top query is now giving me the correct value.

Thanks a bunch!