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.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2011-06-16 : 15:44:44
|
Trying to divide two fields but the following statement is just returning zero for OTP.select TotalRecords, OnRecords, Cast((OnRecords/TotalRecords) as decimal(10,2))as OTPfrom OnTimeShipRecordsThe OnTimeShipRecords table has one record with the following values.TotalRecords OnRecords 337 328 When I run the select statement above its returning:TotalRecords On Records OTP 337 328 0.00 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-16 : 15:48:00
|
You are dividing and THEN casting - which means the division is integer division. Cast and THEN divide - you need to cast at least one of numerator and denominator - for example:select TotalRecords, OnRecords, Cast(OnRecords as decimal(10,2))/TotalRecords as OTPfrom OnTimeShipRecords |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-16 : 18:18:22
|
| People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
Dividing two fields