Odd result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Odd result

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-10-12 : 07:07:44

if total_occupied = 60 and total_bins = 103 then why do I get .00 as a result from this bit of code?

UPDATE A
SET A.percentage_full = (total_occupied / total_bins) * 100
FROM @ResultSet A

I'm a tad confusticated :(

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 07:10:48

Because they are integerss decimal part gets omitted

Try this

UPDATE A
SET A.percentage_full = (total_occupied / total_bins * 1.00) * 100
FROM @ResultSet A

Madhivanan

Failing to plan is Planning to fail

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-10-12 : 07:15:23

Nope, I still get .00

total_bins and total_occupied are type int and percentage_full is decimal(20,2)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-12 : 07:20:23

hope this helps

UPDATE A
SET A.percentage_full = (convert(Float,total_occupied) / Convert(Float,total_bins)) * 100
FROM @ResultSet A

Complicated things can be done by simple thinking

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-10-12 : 07:25:37

KOOL it works :) thnx.

so can you explain why it does not work with int? I could understand it not working if the result type was int and not decimal.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-12 : 07:29:35

when u divide with the integer the values which it comes in point are discardded for eg in ur case if you divide 60/103 which should come somthing 0.58... but since integer doesnt take the decimal value so they are discarded so it round up to 0 and when any number multiple by 0 is 0 :-). so it was giving you 0... :-))..

Complicated things can be done by simple thinking

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-10-12 : 07:31:49

it is so simple!

Cheers :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources