SQLServerCentral » SQL Server Newbies » Rounding Up with given precisionInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralMon, 16 Jan 2017 20:12:04 GMT20Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449628.aspxI would like to round up some figures with given precision. So far I can't find a way to do it. ROUND function doesn't work for me because it can't round up, CEILING doesn't work as well because the results is integer.
What I would like to achieve is a rounding up with give precision of 2 places after dot i.e.
5.0000016 should be 5.01
6.1000138 should be 6.11
7.1200073 should be 7.13
Is it any way to do it in SQL?Tue, 07 May 2013 10:28:14 GMTphoenix_RE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1450242.aspxHere is a solution using the ROUND function:
[code="sql"]with test_data_cte as
(
select datavalue
from
(
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
) dt (datavalue)
)
select
datavalue,
rounded_datavalue =
convert(numeric(8,2),round(datavalue+0.0049999,2))
from
test_data_cte[/code]
Results:
[code="plain"]datavalue rounded_datavalue
--------------------------------------- -----------
5.0000016 5.01
6.1000138 6.11
7.1200073 7.13
5.0000000 5.00
6.1000000 6.10
7.1200000 7.12[/code]Tue, 07 May 2013 10:28:14 GMTMichael Valentine JonesRE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1450204.aspxI came up with another solution based on the same mathematical concept as Lynn's, but I think Lynn's is more elegant:
[code="sql"]with TestData as (
select datavalue
from (
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
)dt(datavalue)
)
select
datavalue,
(ceiling(datavalue * 100) * 1.0) / 100, -- Lynn's idea
CASE WHEN (datavalue * 100) % 1 > 0 THEN ROUND(datavalue, 2, 1) + 0.01 ELSE ROUND(datavalue, 2, 1) END -- My idea
from
TestData;[/code]Tue, 07 May 2013 09:04:12 GMTwolfkilljRE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449806.aspxHere is another option (The CTE is just how I passed in the sample data):
[code="sql"]
with TestData as (
select datavalue
from (
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
)dt(datavalue)
)
select
datavalue,
(ceiling(datavalue * 100) * 1.0) / 100
from
TestData;
[/code]
Mon, 06 May 2013 11:10:33 GMTLynn PettisRE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449672.aspx[quote][b]Jeff Moden (5/6/2013)[/b][hr]Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?[/quote]
This would work as long as it is guaranteed that that each number will have a digit greater then zero in one of the 2 most left digits after the period (Hope that I wrote it correctly in English:-)). Here is an example:
[code]
--with both numbers the user wanted to get 0.01
select ROUND(0.0100000+.00499999,2,1)
select ROUND(0.0000001+.00499999,2,1)
[/code]
AdiMon, 06 May 2013 05:39:58 GMTAdi Cohn-120898RE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449665.aspxHaven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?Mon, 06 May 2013 05:25:03 GMTJeff ModenRE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449634.aspxIt works for me. Thank you very much.Mon, 06 May 2013 03:39:01 GMTphoenix_RE: Rounding Up with given precisionhttps://www.sqlservercentral.com/Forums/FindPost1449630.aspxMaybe there is a better way, but here is one way:
[code]
declare @Num numeric(8,7)
set @Num = 0.0000001
SET @Num = CASE WHEN CAST(@Num AS NUMERIC(3,2)) = @Num THEN @Num ELSE CAST(@Num AS NUMERIC(3,2)) + 0.01 END
SELECT @Num
[/code]
AdiMon, 06 May 2013 03:32:22 GMTAdi Cohn-120898