Database

Peter Leow11-May-14 4:13

Peter Leow

11-May-14 4:13

Yes.

Syafiqah Zahirah11-May-14 4:45

Syafiqah Zahirah

11-May-14 4:45

tqvm for helping me Smile | :)

Syafiqah Zahirah15-May-14 6:50

Syafiqah Zahirah

15-May-14 6:50

Hye peter, this code seems did'nt work..when I execute this SQL to the database directly, it will show no result Frown | :(

Peter Leow15-May-14 14:47

Peter Leow

15-May-14 14:47

Seeing is believing. try this: http://sqlfiddle.com/#!3/1fa93/13028

Arithmetic rounding

Richard.Berry1009-May-14 11:04

9-May-14 11:04

Hi
I need to multiply two columns (float) each of which can have a max of 4 decimal places and round the result to 3 decimal places.

It seems the ROUND(val,decimal_places) uses bankers rounding? (SQL Sever 2008R2) - Although I have read it uses Symmetric Arithmetic Rounding?

Doing the same calculation in Excel gives me the result I need, but Excel uses normal Arithmetic rounding.

How can I do a query to use arithmetic rounding (like Excel)

Examples of the errors:

Price	Qty	Val	ExcelRound	SQLRound
7.5169	745	5600.0905	5600.091	5600.090
4.3465	463	2012.4295	2012.430	2012.429
1.6401	125	205.0125	205.013	205.012
0.0395	369	14.5755	14.576	14.575
5.2349	955	4999.3295	4999.330	4999.329
9.0285	141	1273.0185	1273.019	1273.018
9.0899	645	5862.9855	5862.986	5862.985
3.6167	215	777.5905	777.591	777.590
3.1145	135	420.4575	420.458	420.457
7.4115	105	778.2075	778.208	778.207
7.8675	313	2462.5275	2462.528	2462.527
8.8405	227	2006.7935	2006.794	2006.793
5.4269	55	298.4795	298.480	298.479
1.8833	445	838.0685	838.069	838.068
9.7349	655	6376.3595	6376.360	6376.359
8.6487	365	3156.7755	3156.776	3156.775
1.9033	125	237.9125	237.913	237.912
4.8197	545	2626.7365	2626.737	2626.736

PS: how do you post a table with nicely formatted columns?

modified 9-May-14 17:33pm.

Richard Andrew x649-May-14 12:26

Richard Andrew x64

9-May-14 12:26

I think the general consensus is that it makes more sense to do that kind of formatting in your report, rather than in your database query.

The difficult we do right away...
...the impossible takes slightly longer.

Richard.Berry1009-May-14 20:58

9-May-14 20:58

Hi Richard

Thanks for your reply. In my case here, a customer was requesting a Purchase Order in pdf format, as well as Excel format. I was doing the rounding with a formula in Excel, but the formula did not always fill down to all the rows of the table which is why I decided to do the rounding in the query.

Also, quite often, in Winform apps, I pull a query into a DataTable, and set the DataTable as a DataGridView DataSource, in which case it seems more practical to do the rounding in the query as opposed to looping through the DataTable, and adding the rows with the rounded value to the DataGridView?

Andrius Leonavicius9-May-14 15:02

Andrius Leonavicius

9-May-14 15:02

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.

SQL

SELECT ROUND((7.5169 * 745), 3) AS Result;

SQL

SELECT ROUND(5600.0905, 3) AS Result;

Result: 5600.0910

2. Using FLOAT variables.

SQL

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.

SQL

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

Richard.Berry1009-May-14 20:45

9-May-14 20:45

Hi Andrius

Thanks So much.

Tested below with the same random sample of 300 values, and all agreed with the Excel Values!

SQL

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!

Fetch records efficiently

Andrius Leonavicius10-May-14 4:28

Andrius Leonavicius

10-May-14 4:28

You're welcome. Smile | :)

Best regards,
Andrius Leonavicius

avi_dadi20029-May-14 9:15

avi_dadi2002

9-May-14 9:15

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.

Re: Fetch records efficiently

Richard Andrew x649-May-14 10:30

Richard Andrew x64

9-May-14 10:30

Without seeing the query, how can anyone suggest how to narrow it down?

The difficult we do right away...
...the impossible takes slightly longer.

Re: Fetch records efficiently

Richard MacCutchan9-May-14 21:49

Richard MacCutchan

9-May-14 21:49

avi_dadi2002 wrote:
my query is fetching 500 million records

Seriously? Try calculating how long that would take at 500 records per second.

Need to fetch from two data sources

avi_dadi20029-May-14 9:11

avi_dadi2002

9-May-14 9:11

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.

Re: Need to fetch from two data sources

Richard.Berry1009-May-14 21:40