CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Telecaster
Telecaster

Reputation: 107

Dynamic tsql variable data types causing query to return wrong results

I am using Sql Server 2008

Say you have a table named "Weights" and within that table you have a column named "Weight" with its data type defined as "real" with the following data.

Weight(real data type)

2 2.001 2.002 2.003 2.004 2.005 2.006 2.007 2.008 2.009 3

Here is the query I am running in the new query window against the table

declare @sql nvarchar(MAX)
declare @params nvarchar(MAX)
declare @interval float = .001
declare @conversion float = 1/@interval         

set @sql =
N'select FLOOR(Weight * @INPUTconversion)*@INPUTinterval as [Weight],
COUNT(1) as ''Count''
FROM dbo.Weights
GROUP BY FLOOR(Weight*@INPUTconversion)*@INPUTinterval
order by FLOOR(Weight*@INPUTconversion)*@INPUTinterval'

set @params =
N'
@INPUTconversion real,
@INPUTinterval float'

exec sp_executesql @sql, @params,  
@INPUTconversion = @conversion,
@INPUTinterval = @interval

Here is the result which appears to be wrong.

Weight Count

2 2 2.002 1 2.003 1 2.004 1 2.005 1 2.006 1 2.007 2 2.009 1 3 1

How can I make the return look like this using the same query? Do I need to change my variable data types?

Weight Count

2 1 2.001 1 2.002 1 2.003 1 2.004 1 2.005 1 2.006 1 2.007 1 2.008 1 2.009 1 3 1

Upvotes: 0

Views: 1319

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is clearly a problem of numeric representation in the data. What you are seeing as 2.001 must really be stored as 2.00099999999999 . . . for some number of "9"s.

I think your best solution is to change the data type to something like DECIMAL(12, 6), where what you see is what you get.

Alternatively, you could do some fuzzy math, by adding a very small increment to the value:

select FLOOR(Weight * @INPUTconversion + 0.0000001)*@INPUTinterval as [Weight]

This will treat a weight really close to 2.001 as 2.001 rather than just a smidgen less.

Upvotes: 1

Related Questions