Round up nearest tenths - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Round up nearest tenths

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-29 : 19:41:21

Good day

I have this query...


SELECT '12.36' AS num
UNION ALL
SELECT '102.32' 
UNION ALL
SELECT '1102.99' 
UNION ALL
SELECT '599.99' 
UNION ALL
SELECT '102.33'

Expected result should be...


num
----------------
12.40
102.35
1103.00
600.00
102.35

What I want is to round up the number to the nearest tenths divisible by 5. How should it be done? any idea?

Thanks in advance.

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 20:09:45

"What I want is to round up the number to the nearest tenths divisible by 5"
How did 102.32 round up to 102.35 ? and 102.33 to 102.35 ?

tenth is always divisible by 5

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-29 : 20:41:14

quote:
Originally posted by khtan

"What I want is to round up the number to the nearest tenths divisible by 5"
How did 102.32 round up to 102.35 ? and 102.33 to 102.35 ?

tenth is always divisible by 5

Sorry i will rephrase my question. tenths which i refer here is the placement of the decimal value, which i want to be divisible by 5.

RoundUp take place first after the decimal point.
102.32 round up to 102.35 and 102.33 to 102.35

Second will be before the decimal point. Its either to the nearest tenths, hundreds, thousandths etc...
1102.99 round up to 1103.00 and 599.99 round up to 600.00

Want Philippines to become 1st World COuntry? Go for World War 3...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 20:57:00

Try this. the [nearest 0.10] and [nearest 0.50] are for you reference only


select	n, round(n * 10, 0) / 10 as [nearest 0.10], 
        round(n * 2, 1) / 2 as [nearest 0.50],
	ceiling(n * 20) / 20 [round up to 0.50]
from
(
	select convert(decimal(10,2), num) as n
	from
	(
		SELECT '12.36' AS num 	UNION ALL
		SELECT '102.32'  	UNION ALL
		SELECT '1102.99'  	UNION ALL
		SELECT '599.99' 	UNION ALL
		SELECT '102.33' 
	) a
) b

RESULT :
n            nearest 0.10        nearest 0.50       round up to 0.50      
------------ ------------------- ------------------ --------------------- 
12.36        12.400000           12.350000          12.400000
102.32       102.300000          102.300000         102.350000
1102.99      1103.000000         1103.000000        1103.000000
599.99       600.000000          600.000000         600.000000
102.33       102.300000          102.350000         102.350000

(5 row(s) affected)

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-03-29 : 21:38:26

Nice!

Thanks Khtan

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources