SQL Syntax help in Rounding off to two digits

The below statement works fine but is giving me a result  which is a number with 13 decimal points
rounding off is not working for me . It is giving me round requires 2 or 3 arguments error.

Select b.*,((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount) as WeightedBI,((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount)as WeightedCI

From #Temp2 b

Can anyone answer this?
missatmAsked:
Who is Participating?
 
bradleys40Connect With a Mentor Commented:
try the cast as decimal

Select b.*,cast(((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount) as WeightedBI,((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount)as decimal(8,2))as WeightedCI
0
 
missatmAuthor Commented:
Some how Cast doe not seem to work..Instead this worked

Select b.*,left(((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount),4) as WeightedBI,left(((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount),4)as WeightedCI

From #Temp2 b
0
 
David ToddSenior DBACommented:
Hi,

round ( number, decimals )

Cheers
  David
select 
	b.*
	, round( ((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount), 2 ) as WeightedBI
	, round ((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount), 2 ) as WeightedCI
from #Temp2 b

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
missatmAuthor Commented:
rounding it off is not working for me...it is leaving 9 zeroes after the 2 decimal digits.. For example 6.965432718 becomes 6.960000000 after rounding it to 2 decimal places..It's really weird...
0
 
David ToddSenior DBACommented:
Hi,

Is the rounded value correct?

Then try
      b.*
      , cast( round( ((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount), 2 ) as char( 4 )) as WeightedBI
      , cast( round ((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount), 2 ) as char( 4 )) as WeightedCI
from #Temp2 b

-- assumes that numbers have digit, decimal point, then 2 digits.

Cheers
  David
0
 
missatmAuthor Commented:
Tried that..It is giving me the following error
"The round function requires 2 to 3 arguments"...
0
 
David ToddSenior DBACommented:
Hi,

Can you post some sample data?

Cheers
  David
0
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Example script and results in code snippet.

Adding a value with more digits gives an error. Using varchar() instead of char for the convert appears to work, even without the left function.

Cheers
  David
use tempdb
go
 
if object_id( N'tempdb..#Temp', N'U' ) is not null 
	drop table #Temp;
	
create table #Temp
	(
	t float
	)
	
insert #Temp
select 6.965432718 
union all select 7.326584283 
union all select 15.35893542 
 
select *
from #Temp
 
select cast( round( t.t, 2 ) as char( 4 ))
from #Temp t
 
select convert( char( 4 ), round( t.t, 2 ))
from #Temp t
 
select 
	v.v, left( v.v, charindex( '.', v.v ) + 2 )
from
	(
	select convert( varchar( 20 ), round( t.t, 2 )) as v
	from #Temp t
	) v
 
Results:
 
(3 row(s) affected)
t
----------------------
6.965432718
7.326584283
15.35893542
 
(3 row(s) affected)
 
 
----
6.97
7.33
Msg 232, Level 16, State 2, Line 18
Arithmetic overflow error for type varchar, value = 15.360000.
 
(3 row(s) affected)
 
 
----
6.97
7.33
Msg 232, Level 16, State 2, Line 21
Arithmetic overflow error for type varchar, value = 15.360000.
 
(3 row(s) affected)
 
v                    
-------------------- --------------------
6.97                 6.97
7.33                 7.33
15.36                15.36
 
(3 row(s) affected)

Open in new window

0
 
missatmAuthor Commented:
Please follw this link and you will see the entire query with sample values and results

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23120129.html

0
 
David ToddSenior DBACommented:
Hi,

I'm sorry but I don't see any usuable data I can input into your query.

Cheers
  David
0
 
bradleys40Commented:
Missed the second field in my last statement

Select b.*,cast(((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount)as decimal(8,2)) as WeightedBI,cast(((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount)as decimal(8,2))as WeightedCI
0
 
missatmAuthor Commented:
My Test values are BIAvg =97.31,BIScale=7,BICount=412,CIAvg=93.81,CICount=412,CIScale=6,WOWAvg=100,WOWScale=5,WOWCount=7..Does this help dtodd?

0
 
missatmAuthor Commented:
Thanks for your effort.
0
 
David ToddSenior DBACommented:
Hi,

Thanks for the values.

HTH
  David
use tempdb
go
 
if object_id( N'tempdb..#Temp', N'U' ) is not null 
	drop table #Temp;
	
create table #Temp
	(
	BIAvg float
	, BIScale float
	, BICount int
	, CIAvg float
	, CIScale float
	, CICount int
	, WOWAvg float
	, WOWScale float
	, WOWCount int
	)
 
insert #Temp
	(
	BIAvg 
	, BIScale 
	, BICount 
	, CIAvg 
	, CIScale 
	, CICount 
	, WOWAvg 
	, WOWScale 
	, WOWCount 
	)
	select
		97.31
		, 7
		, 412
		, 93.81
		, 6
		, 412
		, 100
		, 5
		, 7
 
select *
from #Temp
 
select 
--	b.*
--	, 
	convert( varchar( 5 ), round( ((b.BICount * b.BIScale) + (b.WOWCount * b.WOWScale))/(b.BICount + b.WOWCount), 2 )) as WeightedBI
	, convert( char( 5 ), round( ((b.CICount * b.CIScale) + (b.WOWCount * b.WOWScale))/(b.CICount + b.WOWCount), 2 )) as WeightedCI
from #Temp b
 
if object_id( N'tempdb..#Temp', N'U' ) is not null 
	drop table #Temp;
	
Results:
 
(1 row(s) affected)
BIAvg                  BIScale                BICount     CIAvg                  CIScale                CICount     WOWAvg                 WOWScale               WOWCount
---------------------- ---------------------- ----------- ---------------------- ---------------------- ----------- ---------------------- ---------------------- -----------
97.31                  7                      412         93.81                  6                      412         100                    5                      7
 
(1 row(s) affected)
 
WeightedBI WeightedCI
---------- ----------
6.97       5.98 
 
(1 row(s) affected)

Open in new window

0
All Courses

From novice to tech pro — start learning today.