January 29, 2012 at 10:52 pm
andre-394971 (1/29/2012)
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
The modulo function works just fine. You could have the following...
SELECT Name, Address, Amount = Amount - Amount % .05
... and avoid the overhead of a function altogether.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 29, 2012 at 10:57 pm
Sorry I did not "see" you solution at all...my bad
Do you have the time to explain the logic of how/why it works
Many thanks
January 30, 2012 at 12:28 am
I'ts not my solution. JLS posted it.
It uses the MODULO function (% operator) which simply returns the remainder of a division. Try running just Amount % .05 and see what it gives you. You will understand once you see that.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2012 at 12:03 pm
Jeff Moden (1/30/2012)
I'ts not my solution. JLS posted it.It uses the MODULO function (% operator) which simply returns the remainder of a division. Try running just Amount % .05 and see what it gives you. You will understand once you see that.
As a small warning, you may want to confirm what your requirements would be when the number is negative (assuming that would even be allowable). Depending on which way your requirements go - you may need to use FLOOR vs cast or modulo.
notice the output differences when the input is negative:
select cast(-0.999 / 0.05 as int)*.05, floor(-0.999 / 0.05)*.05
, -0.999-(-0.999)%.05
select cast(0.999 / 0.05 as int)*.05, floor(0.999 / 0.05)*.05
, 0.999-(0.999)%.05
----------------------------------------------------------------------------------
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 30, 2012 at 12:15 pm
I see 🙂
Thanks again
January 30, 2012 at 12:17 pm
Thats interesting. You are correct ...there is a small chance that the numbers can be negitive.
Thankyou again
January 30, 2012 at 10:09 pm
I like JLS solution because it’s sleek. However here is another version for simplicity (takes care of negative numbers as well)...
/*
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
*/
with cte_num (num)
as
(
select 158.94
union all
select 158.95
union all
select 158.96
union all
select 158.97
union all
select -158.97
union all
select -158.94
)
select num,
case when num < 0 and (num * (-100) % 10) >= 5 then round(num-1,1) + 0.05 -- Negative Numbers
when num < 0 then round(num,1) -- Negative Numbers
when (num * 100 % 10) >= 5 then round(num,1) - 0.05 -- final cent figure is >= 5
else round(num,1) -- final cent figure is <=4
end roundup
from cte_num
February 1, 2012 at 6:31 pm
SELECT NUM, SIGN(NUM)* FLOOR(ABS(NUM) * 20)/20 AS ROUNDUP
This will handle negative and positive values in the same manner
February 1, 2012 at 6:45 pm
I like that...even simpler again....
Many Thanks....ill do some testing and use this solution...easier to read the sql script...which for my query is quite complicated
February 1, 2012 at 6:53 pm
Read the prior post by Jeff Moden. Using it
DECLARE @Amount Money
SET @Amount = -158.98
SELECT @Amount = @Amount - @Amount % .05
SELECT @Amount
Result:
(No column name)
-158.95
DECLARE @Amount Money
SET @Amount = -$201.83
SELECT @Amount = @Amount - @Amount % .05
SELECT @Amount
Result:
(No column name)
-201.80
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply