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