Need help with data type

[Jenns]

I have a stored procedure that creates a temporary table from another temp table. Then I'm selecting records out of the table to view. (I'm displaying the data in a Cold Fusion application) Here's the code at the end of the SP.
-----------------------------------------
Create table #temp4 (ae3 int, dates int, ms int, total numeric)

Insert into #temp4
( ae3, dates, MS, total)
select ae3, dates, ms, ((dates - ms) /dates) *100
from #temp3

Select ae3, dates, ms, total
from #temp4
order by ae3
--------------------------------------------
My problem is that when the calculation (dates-ms)/ dates returns a number less than 0, example .89, 0 is the value displayed. It displays this way on my web page and also when executed in query analyzer.

Is there a different data type I should be using? I've also tried decimal.

Thanks for any assistance.

 

[foxdev]

Even though .89 isn't less than zero (but I know what you meant), you could probably use the CEILING function, or possibly the ROUND function, to make .89 yield either 0 or 1, depending on your desire.

Robert Bradley

http://www.foxdev.com/

 

[Jenns]

OK, that makes sense. But what if I don't want to round and I want the decimal to show (.89)?

 

[foxdev]

In that case, you may need to define your Total column as Numeric(10,2) or some such.

Robert Bradley

http://www.foxdev.com/