January 28, 2012 at 10:44 pm
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
January 29, 2012 at 6:45 am
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 )
January 29, 2012 at 9:52 am
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.
January 29, 2012 at 9:58 am
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 .....
January 29, 2012 at 1:06 pm
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
January 29, 2012 at 1:21 pm
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
January 29, 2012 at 1:29 pm
/// edit .....still thinking 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 29, 2012 at 1:31 pm
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
January 29, 2012 at 1:50 pm
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
January 29, 2012 at 1:53 pm
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
January 29, 2012 at 2:18 pm
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
January 29, 2012 at 5:37 pm
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.
January 29, 2012 at 9:19 pm
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
January 29, 2012 at 10:34 pm
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?
January 29, 2012 at 10:44 pm
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