Rounding problem in sql 2008

  • Hello There,

    I have been playing around (no pun intended) with the floor and rounding functions in sql 2008 but not getting the result I need

    If i have a dollar amount and if:

    final cent figure is <=4 round down to 0: eg: $201.83 = $201.80

    final cent figure is >= 5 round down to 5: eg $158.98 = $158.95

    Can anyone point me in the right direction....

    I need to perfom the rounding as part of a select statement. eg:

    select Name,Address,Round($Amount,2)......

    many thanks

  • ROUND ( numeric_expression , length [ ,function ] )

    http://msdn.microsoft.com/en-us/library/ms175003.aspx

    CEILING ( numeric_expression )

    http://msdn.microsoft.com/en-us/library/ms189818.aspx

    FLOOR ( numeric_expression )

    http://msdn.microsoft.com/en-us/library/ms178531.aspx

  • Ok when I tried it, I got the same results as you. But what I figured out was use 1 instead of 2. The problem that you are having is that you want to start rounding at the second decimal place but since that is at the end there is nothing to evalutate.

    For example if I did round(20.835, 2) then I would get 20.84

    but round(20.83, 2) would give me 20.83

    Now try round(20.83, 1) and you get 20.80.

  • andre-394971 (1/28/2012)


    Hello There,

    I have been playing around (no pun intended) with the floor and rounding functions in sql 2008 but not getting the result I need

    If i have a dollar amount and if:

    final cent figure is <=4 round down to 0: eg: $201.83 = $201.80

    final cent figure is >= 5 round down to 5: eg $158.98 = $158.95

    Can anyone point me in the right direction....

    I need to perfom the rounding as part of a select statement. eg:

    select Name,Address,Round($Amount,2)......

    many thanks

    One other thing. I am getting thrown off by $Amount. If that is a column then you may not be getting accurate results. Try Select Name, Address, Round(Amount, 1) AS Amount .....

  • Hello There

    Thank you for your reply, Unfortuntaly im not able to get any of the suggetsions you offered to work in all examples. Perhaps I did not make my self clear.

    In The example I gave I need the rounding to produce the following result

    $201.83 must round down to produce $201.80

    $158.98 Must round down to produce $158.95

    With respect to the amount of $201.83 most of the suggestions work a expected BUT I am not able to get any of the suggestions to produce the correct result for second example. In most cases I get either $158.90 or $159.00 but I have not been able to achieve the result im looking for.

    Many thanks

  • just an idea...not sure how efficient this would be on large data sets

    select 201.83 - 201.83 % 0.05

    select 159.98 - 159.98 % 0.05

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • /// edit .....still thinking 🙂

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I could be wrong when I say this but the round function rounds on the tens. Using your example 158.98 would be 159.00.

    The range I believe is

    158.95 - 158.99 = 159.00

    for 158.90 - 158.94 = 158.90

    Again I could be wrong but when running

    SELECT ROUND (158.94, 1)

    I get 158.90

  • Yep..

    158.94 should give me 158.90

    158.95 should give me 158.95

    158.96 should give me 158.95

    158.97 should give me 158.95 etc

    Thanks

  • andre-394971 (1/29/2012)


    Yep..

    158.94 should give me 158.90

    158.95 should give me 158.95

    158.96 should give me 158.95

    158.97 should give me 158.95 etc

    Thanks

    ..so what solution are you using?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Ok I have tried

    SELECT ROUND(158.94,1,2) = 158.90...This is what I want

    SELECT ROUND(158.97,1,2) = 158.90 This is NOT what I need I need to get 158.95

    Just seem to be missing something in my understanding

    or am i going to have to do something complicated and do a check to see if the last decimal diget is 1,2,3 or 4 and round to .#0 or if the diget is 5,6,7,8,9 round to .#5

    Thanks

  • This might get you thinking (especially how to improve the code I have posted below). Seems I got my mind in a rut and could come up with nothing but this. That said there must be a better way. But it does what you required. Now if you have a large number of rows to process, well kick off your T-SQL, and go out for a coffee break, it might be finished after you get back to your desk.

    DECLARE @V2 MONEY

    DECLARE @M2 MONEY

    SET @V2 = 158.90

    IF SUBSTRING(CAST(@V2 AS VARCHAR(10)),DATALENGTH(CAST(@V2 AS VARCHAR(10))),1) IN(0,1,2,3,4)

    BEGIN

    SET @M2 = CAST(SUBSTRING(CAST(@V2 AS VARCHAR(10)),1,DATALENGTH(CAST(@V2 AS VARCHAR(10)))-1)+'0' AS MONEY)

    SELECT @M2

    END

    ELSE

    BEGIN

    SET @M2 = CAST(SUBSTRING(CAST(@V2 AS VARCHAR(10)),1,DATALENGTH(CAST(@V2 AS VARCHAR(10)))-1)+'5' AS MONEY)

    SELECT @M2

    END

    My other suggestions are:

    1. Examine the business reason for this "adjustment"

    2. Decide is it can be better performed on the front end.

    2a. If tne front end can not be altered, then consider changing the table containing these values, by adding a column which would contain the adjusted value. Go thru the table once and populate that new column (admittedly it may be a long task but once done the adjusted value can be selected by the T-SQL select statement.)

    2b. When adding or updatting a row or rows, considerd updating the adjusted value at the time thus avoiding a prolonged step 2a task.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks for that...

    The number of rows I need to process is less than 1000 in the worst case...

    Yes it would be possible to do on the front end but I kind of figured there must be a way to do it on the backend 🙂

    For some reason the Client wants the rounding done this way....

    Now...Can you suggest a way to turn this bit of script into a function?

    My Knowledge of SQL Server 2008 is limited

    Cheers

  • andre-394971 (1/29/2012)


    Thanks for that...

    The number of rows I need to process is less than 1000 in the worst case...

    Yes it would be possible to do on the front end but I kind of figured there must be a way to do it on the backend 🙂

    For some reason the Client wants the rounding done this way....

    Now...Can you suggest a way to turn this bit of script into a function?

    My Knowledge of SQL Server 2008 is limited

    Cheers

    Did you not try the modulo solution above? looks to me to be what you're looking for.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hello There,

    The solution proposed works as expected BUT I carnt use it as is.

    I need to be able to perform a statement similar to the below

    Select Name,Address, Function(Amount) as Amount.....

    Many Thanks

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply