June 8, 2004 at 11:50 am
Hello everyone and THANK YOU for this site. I have learned so much from it. My question is:
how do I add 5 to the number(money data type) and round it up to the nearest $5 dollar threshold.
select round(18.50 +5,0) this should be 25
select round(24 +5,0) this should be 30
Thank you very much for your help
M.R.
June 8, 2004 at 12:07 pm
Clunky I know but this should work
DECLARE @Var INT
SET @Var = (SELECT ROUND(18.50, 0) + 5)
SELECT
CASE WHEN @Var >= 0 AND @Var <= 5 THEN 5
WHEN @Var > 5 AND @Var <= 10 THEN 10
WHEN @Var > 10 AND @Var <= 15 THEN 15
WHEN @Var > 15 AND @Var <= 20 THEN 20
WHEN @Var > 20 AND @Var <= 25 THEN 25
WHEN @Var > 25 AND @Var <= 30 THEN 30
WHEN @Var > 30 AND @Var <= 35 THEN 35
END
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 8, 2004 at 12:43 pm
This may be a good case for using a UDF to hide the ugly calculation below. The expression can probably be simplified a bit, but I'll leave that as an exercise for the student.
Cheers,
/Wayne
DECLARE @BaseAmount MONEY,
@BaseAmountPlus MONEY
SET @BaseAmount = 16.00
SET @BaseAmountPlus = @BaseAmount + 5.0
SELECT @BaseAmount, @BaseAmountPlus, ROUND(@BaseAmount,0,1) + 10-(CAST(ROUND(@BaseAmount,0,1)AS INT) % 5)
SET @BaseAmount = 18.50
SET @BaseAmountPlus = @BaseAmount + 5.0
SELECT @BaseAmount, @BaseAmountPlus, ROUND(@BaseAmount,0,1) + 10-(CAST(ROUND(@BaseAmount,0,1)AS INT) % 5)
SET @BaseAmount = 24.00
SET @BaseAmountPlus = @BaseAmount + 5.0
SELECT @BaseAmount, @BaseAmountPlus, ROUND(@BaseAmount,0,1) + 10-(CAST(ROUND(@BaseAmount,0,1)AS INT) % 5)
SET @BaseAmount = 29.99
SET @BaseAmountPlus = @BaseAmount + 5.0
SELECT @BaseAmount, @BaseAmountPlus, ROUND(@BaseAmount,0,1) + 10-(CAST(ROUND(@BaseAmount,0,1)AS INT) % 5)
June 8, 2004 at 12:48 pm
Here is a slightly more generic version:
DECLARE @BaseAmount MONEY,
@BaseAmountPlus MONEY,
@Increment INT
SET @Increment = 5.0
SET @BaseAmount = 16.00
SET @BaseAmountPlus = @BaseAmount + @Increment
SELECT @BaseAmount, @BaseAmountPlus, ROUND(@BaseAmount,0,1) + (2*@Increment)-(CAST(ROUND(@BaseAmount,0,1)AS INT) % @Increment)
June 8, 2004 at 1:33 pm
This function will round any number to any base you chose. So in your case just pass number + 5, 5 to get your desired results:
CREATE FUNCTION RoundToBase(@value money, @Base money)
RETURNS Money
AS
BEGIN
DECLARE @Answer money
set @Answer = Convert(int, (@value / @Base)) * @Base
RETURN(@Answer)
END
June 8, 2004 at 1:48 pm
usage syntax is Select dbo.RoundToBase(16 + 5, 5)
June 8, 2004 at 2:42 pm
So much help in so little time. Thank you very much everyone.
Very grateful
M.R.
June 9, 2004 at 9:44 am
You can most likely make this more efficient, but here is a first draft.
Enjoy,
Charaka
select Ceiling(cast((Cast(24 as float) + cast(5 as float)) as numeric)/5) * 5
June 9, 2004 at 9:45 am
Edward,
Beautiful function! Performs rounding just as advertised! Miriana's requirement was for rounding up, so if we add "set @Value = @Value + (@Base / 2.001)", it will always round up.
CREATE FUNCTION RoundToBase(@value money, @Base money)
RETURNS Money
AS
BEGIN
DECLARE @Answer money
set @Value = @Value + (@Base / 2.001)
set @Answer = Convert(int, (@value / @Base)) * @Base
RETURN(@Answer)
END
-- Steve
June 9, 2004 at 11:37 am
hoo-t i have to level with you, I was amazed at how simple I could make it. It was all those math courses in college I think.
June 9, 2004 at 11:44 pm
I dunno... call me "old school"... This will always round up to the base as well.
SELECT ROUND(@Value/@Base+.499999,0)*@Base
Also, just curious... why would you need to round up to the nearest $5? Enquiring minds want to know...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy