## Rounding Up with given precision

 Author Message phoenix_ SSC Veteran Group: General Forum Members Points: 234 Visits: 31 I 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.016.1000138 should be 6.117.1200073 should be 7.13Is it any way to do it in SQL? Adi Cohn SSCoach Group: General Forum Members Points: 19366 Visits: 6653 Maybe there is a better way, but here is one way:`declare @Num numeric(8,7)set @Num = 0.0000001SET @Num = CASE WHEN CAST(@Num AS NUMERIC(3,2)) = @Num THEN @Num ELSE CAST(@Num AS NUMERIC(3,2)) + 0.01 ENDSELECT @Num`Adi --------------------------------------------------------------To know how to ask questions and increase the chances of getting asnwers:http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ phoenix_ SSC Veteran Group: General Forum Members Points: 234 Visits: 31 It works for me. Thank you very much. Jeff Moden SSC Guru Group: General Forum Members Points: 505684 Visits: 44254 Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you? --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Adi Cohn SSCoach Group: General Forum Members Points: 19366 Visits: 6653 Jeff Moden (5/6/2013)Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?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:`--with both numbers the user wanted to get 0.01select ROUND(0.0100000+.00499999,2,1)select ROUND(0.0000001+.00499999,2,1) `Adi --------------------------------------------------------------To know how to ask questions and increase the chances of getting asnwers:http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ Lynn Pettis SSC Guru Group: General Forum Members Points: 223166 Visits: 40398 Here is another option (The CTE is just how I passed in the sample data):`with TestData as (select datavaluefrom (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) / 100from TestData;` Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) wolfkillj SSCertifiable Group: General Forum Members Points: 5506 Visits: 2582 I came up with another solution based on the same mathematical concept as Lynn's, but I think Lynn's is more elegant:`with TestData as (select datavaluefrom (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 ideafrom TestData;` Jason WolfkillBlog: SQLSouthTwitter: @SQLSouth Michael Valentine Jones SSC-Dedicated Group: General Forum Members Points: 35746 Visits: 11933 Here is a solution using the ROUND function:`with test_data_cte as(select datavaluefrom ( 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`Results:`datavalue rounded_datavalue--------------------------------------- -----------5.0000016 5.016.1000138 6.117.1200073 7.135.0000000 5.006.1000000 6.107.1200000 7.12`