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 |
|
SirPaddi
Starting Member
2 Posts |
 Posted - 2006-02-08 : 06:49:04
|
| Hi All,I've been working on SQL Server 2000 (sp3) for quite some time but never come across this before...I’m calculating a percentage based on 2 decimals in the database...Table1Period ........ int (e.g. 200601, 200602, etc)Revenue ....... [decimal] (18,3)TotalRevenue .. [decimal] (18,3)Table2Period ........ int (e.g. 200601, 200602, etc)Revenue ....... [decimal] (18,3)TotalRevenue .. [decimal] (18,3)PercRevenue ... [decimal] (19,16)My SQL Command is quite simple Insert Into Table2Select Period, Sum(Revenue), TotalRevenue, Sum(Revenue) / Total_Revenue As PercRevenue From Table1 Group By Period, TotalRevenueThe result of PercRevenue seems to be Truncated at 6 decimals (not rounded to), even though I store it in a ‘[decimal] (19,16)’ field. The problem is that I need to use this result in further calculations and when dealing with large numbers it starts to throw the results out quick considerably.As I said, I’ve never come across this before, so I’d appreciate any help I can get.Thank you,Michael |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
SirPaddi
Starting Member
2 Posts |
Posted - 2006-02-09 : 06:12:44
|
| Thank you for the pointer (I didn't find it from the searches I did)Although I had come across the same thing (as described in the forum topic) in the 'Books Online' I didn't want to use floats because I'm dealing with financial data. If I convert the values to floats in the Select Clause the calculations, although more accurate than storing the values as floats, still lose a certain amount of precision. As far as I can tell this is becuase of the way SS2K deals with floats? |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-09 : 06:17:42
|
It is by nature of the data type.From Books OnLinequote:
float and realApproximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.Syntaxfloat [ ( n ) ]Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.n is Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).realFloating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
----------------------------------'KH'everything that has a beginning has an end |
|
|
|
|
|
|
|
|
Dividing two decimals