help, SQL Stored procedure - ASTM rouding rule

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

help, SQL Stored procedure - ASTM rouding rule

Author

Topic

carlospro7
Starting Member

5 Posts

Posted - 2008-06-26 : 22:33:18

I don't have much SQL experience, this is my problem:

I have to write an SQL procedure that will apply ASTM rounding rules (or i guess also known as round-to-even method [url]http://en.wikipedia.org/wiki/Rounding#Round-to-even_method[/url]. This page explains this method) to a column of floats (i.e. results) in a table and write the result of that application into another column of the table (i.e. ASTM_results). There is a column that specifies how many decimal places it should be rounded to (spec).

The starting table may look something like this


---------------------------------------
|  RESULTS  |  SPEC  |  ASTM_RESULTS  |
---------------------------------------
|   3.016   |    2   |      NULL      |
|   3.013   |    2   |      NULL      |
|   3.015   |    2   |      NULL      |
|   3.045   |    2   |      NULL      |
|  3.04501  |    2   |      NULL      |

After the ASTM rules have been applied (through the procedure) this is what the above table should look like


---------------------------------------
|  RESULTS  |  SPEC  |  ASTM_RESULTS  |
---------------------------------------
|   3.016   |    2   |      3.02      |
|   3.013   |    2   |      3.01      |
|   3.015   |    2   |      3.02      |
|   3.045   |    2   |      3.04      |
|  3.04501  |    2   |      3.05      |

It should apply it to every row and add the rounded number to the ASTM_RESULTS field in its corresponding row. I hope that makes sense.

Like I said I don't know much SQL, but I have to apply this to a table in SQL. If anyone can help it would be much appreciated.[code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 23:40:00

1st draft

-- http://en.wikipedia.org/wiki/Rounding#

ROUND-to-even_method
IF object_id('dbo.round_to_even') IS NOT NULL
BEGIN
	DROP FUNCTION dbo.round_to_even
END
go

CREATE FUNCTION dbo.round_to_even
(
	@val		decimal(30, 10),
	@decimals	int
)
RETURNS decimal(30, 10)
AS
BEGIN
	DECLARE	@power		bigint,
		@digit		int,
		@p_digit	int

	SELECT	@power	= power(10, @decimals)
	SELECT	@digit	= CONVERT(bigint, (@val * @power * 10)) % 10,
		@p_digit= CONVERT(bigint, (@val * @power)) % 10

	RETURN 
	(
		SELECT
		CASE 	WHEN @digit >= 6 	
			THEN (CONVERT(bigint, @val * @power) + 1) * 1.0 / @power
			WHEN @digit <= 4	
			THEN CONVERT(bigint, @val * @power) * 1.0 / @power
			WHEN @digit = 5
			THEN 
				CASE 	WHEN 	@val > CONVERT(bigint, @val * @power * 10) * 1.0 / (@power * 10)
					THEN	(CONVERT(bigint, @val * @power) + 1) * 1.0 / @power
					WHEN 	@p_digit % 2 = 1
					THEN	(CONVERT(bigint, @val * @power) + 1) * 1.0 / @power
					ELSE	CONVERT(bigint, @val * @power) * 1.0 / @power
					END
			END
	)
END
go

SELECT	val, rte = dbo.round_to_even(val, decimals)
FROM
(
	SELECT	val = 3.016, 	decimals = 2	UNION ALL
	SELECT	val = 3.013, 	decimals = 2	UNION ALL
	SELECT	val = 3.015, 	decimals = 2	UNION ALL
	SELECT	val = 3.045, 	decimals = 2	UNION ALL
	SELECT	val = 3.04501, 	decimals = 2
) v

/*
val      rte                              
-------- --------------
 3.01600  3.0200000000 
 3.01300  3.0100000000 
 3.01500  3.0200000000 
 3.04500  3.0400000000 
 3.04501  3.0500000000 

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-26 : 23:55:52

Basically same code, just re-organize a bit.

-- http://en.wikipedia.org/wiki/Rounding#ROUND-to-even_method

IF object_id('dbo.round_to_even') IS NOT NULL
BEGIN
	DROP FUNCTION dbo.round_to_even
END
go

CREATE FUNCTION dbo.round_to_even
(
	@val		decimal(30, 10),
	@decimals	int
)
RETURNS decimal(30, 10)
AS
BEGIN
	DECLARE	@power		bigint,
		@digit		int,
		@p_digit	int,
		@adj		int

	SELECT	@power	= power(10, @decimals)
	SELECT	@digit	= CONVERT(bigint, (@val * @power * 10)) % 10,
		@p_digit= CONVERT(bigint, (@val * @power)) % 10,
		@adj	= 0

	SELECT	@adj	= 
		CASE 	WHEN @digit >= 6 
			THEN 	1
			WHEN @digit = 5
			THEN 
				CASE 	WHEN 	@val > CONVERT(bigint, @val * @power * 10) * 1.0 / (@power * 10)
					THEN	1
					WHEN 	@p_digit % 2 = 1
					THEN	1
					ELSE  	0
					END
			ELSE 	0
			END


	RETURN 
	(
		SELECT (CONVERT(bigint, @val * @power) + @adj) * 1.0 / @power
	)
END
go

SELECT	val, rte = dbo.round_to_even(val, decimals)
FROM
(
	SELECT	val = 3.016, 	decimals = 2	UNION ALL
	SELECT	val = 3.013, 	decimals = 2	UNION ALL
	SELECT	val = 3.015, 	decimals = 2	UNION ALL
	SELECT	val = 3.045, 	decimals = 2	UNION ALL
	SELECT	val = 3.04501, 	decimals = 2
) v

/*
val      rte                              
-------- --------------
 3.01600  3.0200000000 
 3.01300  3.0100000000 
 3.01500  3.0200000000 
 3.04500  3.0400000000 
 3.04501  3.0500000000 

(5 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

carlospro7
Starting Member

5 Posts

Posted - 2008-06-28 : 01:32:05

khtan,

Thank you for replying with your knowledge. The code seems to work just fine, and seems to apply the rules as intended. After some modifications I was able to have it running on the data that I needed it to.

What I like about your solution is that it uses simple SELECT statements and such, and so it helped better understand how SQL works after walking through the code.

Also, thanks for the quick reply. Have a good one

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-28 : 06:06:36

You are welcome.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources