Conversion failed when converting varchar to data type int

[747576]

Hi,
I have a piece of sql that composites data to a specified interval (@compositeby).

If I want to composite the data in the table by a float value (not an integer) then I get an error.

In the code below the @compositeby is set as 2.5, but it gives me an error:

Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value '2.5' to data type int.

I have tried to declare the @compositeby as a float but it doesn't give the required results.
How do I fix this.

Thank you.

create table increment(id varchar(10),increment float,value float)

insert into increment(id,increment,value) values('1a','0.1','7')
insert into increment(id,increment,value) values('1a','0.5','4')
insert into increment(id,increment,value) values('1a','1','6')
insert into increment(id,increment,value) values('1a','1.5','2')
insert into increment(id,increment,value) values('1a','2','5')
insert into increment(id,increment,value) values('1a','2.5','8')
insert into increment(id,increment,value) values('1a','3','5')
insert into increment(id,increment,value) values('1a','3.5','1')
insert into increment(id,increment,value) values('1a','4','2')
insert into increment(id,increment,value) values('1a','4.5','3')
insert into increment(id,increment,value) values('1a','5','5')
insert into increment(id,increment,value) values('1a','5.5','4')
go

declare @compositeby varchar(30)

set @compositeby = 2.5

select id, inc_from, inc_to  
, comp_val=(select sum(value) from increment where increment between tbl.inc_from and tbl.inc_to)/(select count(*) from increment where increment between tbl.inc_from and tbl.inc_to)  
from
(select	id
		, inc_from=case when round(cast(increment as int)/@compositeby, 0)*@compositeby < (select min(increment) from increment) then (select min(increment) from increment) else round(cast(increment as int)/@compositeby, 0)*@compositeby end
		, inc_to=case when round(cast(increment as int)/@compositeby, 0)*@compositeby + @compositeby > (select max(increment) from increment) then (select max(increment) from increment) else round(cast(increment as int)/@compositeby, 0)*@compositeby + @compositeby end
		, value
from increment) tbl
group by id, inc_from, inc_to

drop table increment

 

[gmmastros]

try declaring @compositeby to a number data type.

ex:

declare @compositeby [!]Decimal(10,1)[/!]

set @compositeby = 2.5

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

I believe this is the issue:

select id
, inc_from=case when round(cast(increment as int)/@compositeby, 0)*@compositeby

You are casting Increment as INT and dividing that by @compositeby. They need to be the same data type.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[747576]

Thanks for the advice,
If I declare @compositeby as Decimal(10,1) then if set compositeby = 2 I get the wrong results.

The results I get are:
1a 0.1 2 4.8
1a 2 4 4.2
1a 4 5.5 3.5
1a 6 5.5 NULL

it should be:
1a 0.1 2 4.8
1a 2 4 4.2
1a 4 5.5 3.5

If the increment and compositeby are the same data type i.e. int then it works but I cant use a decimal for compositeby.
Is there a way to over come this?
Thank you

 

[simian336]

Try

set compositeby = 2.0

Simi