|
Hi,
The problem is not a ROUND function, but the precision of the FLOAT data type (Using decimal, float, and real Data[^]). You have at least a couple of options:
1. Use DECIMAL or NUMERIC instead of the FLOAT.
2. CAST to DECIMAL for the calculation.
Here's the demonstration for you (using your first example):
1. Select using ROUND.
SELECT ROUND((7.5169 * 745), 3) AS Result;
or
SELECT ROUND(5600.0905, 3) AS Result;
Result: 5600.0910
2. Using FLOAT variables.
DECLARE @a FLOAT, @b FLOAT, @c FLOAT;
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;
Result: 5600.09
3. Using DECIMAL variables.
DECLARE @a DECIMAL(18, 4), @b DECIMAL(18, 4), @c DECIMAL(18, 4);
SET @a = 7.5169;
SET @b = 745;
SET @c = @a * @b;
SELECT ROUND(@c, 3) AS Result;
Result: 5600.0910
P.S. I am posting such data with the XML pre tag. Also, I am inserting (copying) TAB characters (if needed).
Best regards,
Andrius Leonavicius
|
|
|
|
|
Hi Andrius
Thanks So much.
Tested below with the same random sample of 300 values, and all agreed with the Excel Values!
select price
, qty
, price*qty as Val
, round(CONVERT(decimal(12,4),price)* CONVERT(decimal(12,4),qty),3) as RoundVal
from dbo.[round]
What mis-lead me into thinking the problem was with the type of rounding was that from my sample of 300 values, all the values that differed had a 5 in the 4th decimal place, and all the errors were not rounded as expected.
In the link you poseted:
Using float and real Data
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
...
The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.
Thanks for your help!
|
|
|
|
|
You're welcome. 
Best regards,
Andrius Leonavicius
|
|
|
|
|
My Table has millions of records and my query is fetching 500 million records for 1 day to the SSRS report. so, It is taking too long time to fetch the data. Could anybody can suggest me how to narrow down or improve my query.
Thanks in Advance,
Avinash.
|
|
|
|
|
Without seeing the query, how can anyone suggest how to narrow it down?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
avi_dadi2002 wrote: my query is fetching 500 million records
Seriously? Try calculating how long that would take at 500 records per second.
|
|
|
|
|
Hi,
I need to fetch data from two different data sources in a single select query in DB2 environment. For Example.
My DB@ env has two are different data sources named as XXX5T and XXXXX5P.
Each data source(Host) has multiple databases, i want to fetch some records from two different tables, one table is available in XXX5T and 2nd one exist in XXXXX5P.
Select p,q,r from XXX5T.MyDB5T.MyTable a
INNER JOIN XXX5P.MyDB5P.MyTable b on a.id= b.id.
Thanks in advance.
|
|
|
|
|
Sorry - never read your question properly... (Not sure about DB2)
but in any case...
Perhaps UNION ALL is what you are looking for.
(Columns in both selects must be the same)
select p, q, r
from XXX5T.MyDB5T.MyTable
UNION ALL
select p, q, r
from XXX5T.MyDB5P.MyTable
also see Linked Servers http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]
modified 10-May-14 4:08am.
|
|
|
|
|
We have a relational DB in SQL Server 2008 which grinds up monthly data sets based on a set of criteria, these runs can take between 2 and 4 hours to produce and there may be 10-12 per month in 3-4 run sets. I am proposing that each run set has a cube for reporting and analysis.
The results are stored in 2 (fact) tables, each table has a view which pulls in the dimension fields, date, branch, product etc.
Each Run can result in +7m rows
Q1 Should I replicate the data into a reporting database before building the cube or build direct from the relational DB?
Q2 It has been recommended that I use the views, do I also need to pull in the dimension tables or can I use the fields in the view? eg Product dimension is serviced by a distinct of the Product column (answer - use the dimension table for cases where there is no record for a dimension element this period  ).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am currently working on dash board application, I have a table say ‘User_data_master’
User_data_master(‘id’, ‘uid’, ‘data’, ‘instid’,’deptid’)
Id-primary key
uid- user id referred to user bio data
data- data posted
deptid- department id
instid- institute id
Above table will store data for different employee from different department of an institution
In select query I need to get my rows belongs to me (I have saved), Then people from my department,
Then rest of rows which belongs to my Institute.
Any help is appreciated mostly
|
|
|
|
|
|
Hello Everyone
In my application there is a login and logout functionality.if i forget to logout and logout next day. i am unable to get the accurate difference between login and logout at backend..please can anyone help me
Thank you
|
|
|
|
|
Check the DAY component of the logout datetime value!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a database(Eg. SALES) in SQL Server 2014 which needs to be recreated in SQL Server 2012 version. Since I can not use the SQL 2014 back up file in SQL 2012 environment, I have proceed with the Generate Script option and trying to run the script in SQL Server 2012 environment. However it is giving an error as Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'DELAYED_DURABILITY'. which is pointing to the ALTER DATABASE [SALES] SET DELAYED_DURABILITY = DISABLED line in the script and not able to proceed further. Please help me to get this sorted out.
Thanks,
KV
|
|
|
|
|
As mentioned in the Lounge, remove that setting as it is new to 2014.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I have a database(Eg. SALES) in SQL Server 2014 which needs to be recreated in SQL Server 2012 version. Since I can not use the SQL 2014 back up file in SQL 2012 environment, I have proceed with the Generate Script option and trying to run the script in SQL Server 2012 environment. However it is giving an error as Msg 102, Level 15, State 6, Line 1
Incorrect syntax near 'DELAYED_DURABILITY'. which is pointing to the ALTER DATABASE [SALES] SET DELAYED_DURABILITY = DISABLED line in the script and not able to proceed further. Please help me to get this sorted out.
Thanks,
Kala
modified 5-May-14 14:45pm.
|
|
|
|
|
You are in the wrong forum!!!
Welcome to the Lounge
Technical discussions are welcome, but if you need specific help please use the programming forums.
DELAYED_DURABILITY is a 2014 only - new - feature, so 2012 do not know it...Change database compatibility to 2012 (110) and try again...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Correct and Correct. +5 
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Didn't notice the forum. Thank you.
KV
|
|
|
|
|
|
Lloyd Atkinson wrote: I don't know what the equivalent is in 2012.
It is empty string. 
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
As far as I know, there is no such property DELAYED_DURABILITY in SQL 2012. Hence removed that line and ran the query, it works fine. Not sure if this is the right approach, but just tried it out.
Thanks,
KV
|
|
|
|
|
There is not such property. Removing it is just right...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thank you Kornfeld Eliyahu Peter.
KV
|
|
|
|
|
hi,
I want to have fields in MySQL such as is_administrator, is_active, is_blocked which will be true or false, yes or no, negative or positive, or whatever name you'll use for it..
I would like to ask what's the best field type to use for such requirment?
Thanks,
Jassim
Technology News @ www.JassimRahma.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
