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 |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-01-12 : 14:13:06
|
| Take me to school, please..I want to do a simple division of two fields in a select statement.value1 = 3928 (int)value2 = 8854 (int)select (value1/value2) as MetricValue, value1, value2 From ScoreCard, Where MetricID=17my result is metricvalue is 0.The goal is to do an update to generate the value once a week (question if this is the best way to produce weekly metric, or something like analysis server should be used?)..update scorecardmetrics set metricvalue=(value1/value2) where metricID=17metricvalue is float, value1 and value2 are int... |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-12 : 14:28:25
|
[code]declare @value1 int, @value2 intset @value1=3928 set @value2=8854 select convert(float,@value1) / convert(float,@value2)[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-12 : 14:30:20
|
| yep...worked like a champ...so I need to have all values of the same type (at least intergers divided by intergers)...thank you!SELECT convert(float,value1)/convert(float,value2) as metriccalc,KeyID, MetricID, WeekOfCalYr, Year, Target, Value1, Value2, Value3, Value4, Value5, MetricValue, Updated, EnteredFROM ScoreCardMetricsWHERE (MetricID = 17)update scorecardmetrics set metricvalue=convert(float,value1)/convert(float,value2) where metricid=17 |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-01-12 : 15:06:18
|
| No, the key thing is not that all values be the same type. The key thing is that an integer divided by another integer yields an integer result. At least one of the values must be non-integer to get real number results.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-13 : 00:03:50
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-01-13 : 03:26:35
|
I would however stay away from the float datatype if I were you. In this case it seems to work fine but it can give some peculiar and unwanted results. From BOL: quote:
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
Use decimal or numeric instead.- Lumbago |
|
|
|
|
|
|
|
|
Simple Math