Precision/Scale Problem? 2

[Coder7]

Hello.

Would one of you great people please help me?

When I run this query, @score s/b 66.7 but 67 is printing (so I am assuming that the value that's being returned for @score = 67) but I can't figure out why.

I appreciate your help, as always

--------------------
Declare @s1 FLOAT
Declare @s2 FLOAT
Declare @tx_id INT
Declare @score decimal

Set NOCOUNT on

--Set @tx_id = LTrim( RTrim( @tx_id) )
Set @tx_id = LTrim( RTrim( '79004') )

--Get the sum of the actual question answer values
SELECT @s1 = convert(numeric(8,4),sum(cast(ques.answer_value AS INTEGER)))
FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans
WHERE ques.score_value_ind = 'S'
and tx_id = @tx_id
and ques.code = ans.code
and ques.answer_value = ans.answer_value
and ans.valid_answer <> 'NA'

SELECT @s2 = sum(ques.max_answer_value)
FROM QUESTION_DATA AS ques
WHERE tx_id = @tx_id
AND max_answer_value is not null


SELECT @score = convert(decimal(4,1),convert(decimal(4,1),(@s1*100)) / convert(decimal(4,1),@s2))

print @s1
print @s2
print @score
--------------

 

[mrdenny]

Your problem is in the declare.

Declare @score    decimal
/*should be*/
Declare @score    decimal(4,1)

If you don't define the precision and scale it defaults to something like (8,0) or something.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Coder7]

Thank you Mr. Denny. That worked perfectly. Can you tell it's the first time I'm working with decimal data type? haha. I have read alot but I missed this (obviously).
-----------------
Another question, if you don't mind.

Would it be better to define @s1 and @s2 as integers rather than float? I have to cast ques.answer_value AS INTEGER because it is varchar in the sql server table. I don't have to cast ques.max_answer_value because it is INTEGER data type in the sql server table.

P.S. I tried to give you a star but got an error sorry.

Thanks, again, and have a great weekend

 

[dbomrrsm]

coder7

I have given mrDenny a star on your behalf

[blue]DBomrrsm[/blue]

 

[SQLBill]

You don't want @s1 to be INT datatype. Integer is a whole number and the value being put into @s1 is a decimal value. So making @s1 an INT would change the value of the input.

You CONVERT both @s1 and @s2 to decimal values in the last select. Make them decimal to begin with. When you create a datatype for a value always ask yourself, what value am is this going to be? And what value do I want it to be?

In your case the values are decimals and they end up being decimals. They are never FLOAT.

Check out the BOL for more information on DATATYPES and CONVERT.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[mrdenny]

I would have to agree with SQLBill.

Thanks DBomrrsm.

Denny

--Anything is possible. All it takes is a little research. (Me)