Simple math operation in SQL fails...why?

[mbabcock]

I'm trying to get a percentage based on one column divided by another. I do this in VFP easily but need to do it now in a SQL Server 2000 stored proc.

Here’s the output from the SP:



cSalesRepID iTotQtyStatused iTotQtySold iTotQtyFinalSold iTotQtyCancelled nPctSold nPctCancelled

--------------- --------------- ----------- ---------------- ---------------- ------------- -------------

AZIMMERMAN 34 0 0 0 .0 .0

BLEACH 110 5 1 4 .0 .0

DMORRISON 65 21 10 11 .0 .0

JPUZSAR 5 3 2 1 .0 .0

JTROTTER 21 10 7 3 .0 .0

LHERSHBERGER 2 0 0 0 .0 .0

SBOWMAN 33 0 0 0 .0 .0

SDROHAN 26 4 2 2 .0 .0





@RETURN_VALUE = 0



Here’s my logic for calculating the nPCT(x) columns into the final result (where c2 is the alias for all columns except the nPCTs):





select c2.*,

nPctSold = case when iTotQtyStatused > 0

then (iTotQtyFinalSold / iTotQtyStatused) * 100

else 000.00 end,

nPctCancelled = case when iTotQtyStatused > 0

then (iTotQtyCancelled / iTotQtyStatused) * 100

else 000.00 end

from #TempCounts2 c2



Now (11 / 65) * 100 (in the case of DMORRISON) should be 16.9 percent (rounded to tenths). Why does my logic above fail?



Tia!
--Michael

 

[SQLDenis]

Integer math

try this

select c2.*,

            nPctSold = case when iTotQtyStatused > 0 

                        then (iTotQtyFinalSold / (iTotQtyStatused*1.0)) * 100

                        else 000.00 end,

            nPctCancelled = case when iTotQtyStatused > 0 

                        then (iTotQtyCancelled / (iTotQtyStatused*1.0)) * 100

                        else 000.00 end

from #TempCounts2 c2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mbabcock]

BAM...That's it! Thanks!