June 17, 2015 at 4:11 am
Hello comunity
I have the following question about rounding, i need this calculation:
50.99 must return 50.00
53.58 must return 50.00
55.23 must return 55.00
55.00 must retunr 55.00
57.58 must return to 55.00
in pratice if the the integer value is equal to 5.xx then i do make a floor otherwise if the integer value is greather than 5.xx, i must rounding down to 5.00.
The scale for rounding is between 0 and 5.
how can do that ?
Many thanks
Luis Santos
June 17, 2015 at 4:16 am
Edit: ignore, misread question.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2015 at 4:30 am
Hello
Sorry but i don´t understand your answer.
What i can do is possible, i need rounding numbers in multiples of 0 and 5 in sql server, independently of decimal values.
Thanks
Luis Santos
June 17, 2015 at 4:30 am
This should do:
DECLARE @values TABLE (
someValue float,
expectedOutput float
)
INSERT INTO @values
VALUES
(50.99, 50.00),
(53.58, 50.00),
(55.23, 55.00),
(55.00, 55.00),
(57.58, 55.00)
SELECT *, calcValue = FLOOR(someValue / 5) * 5
FROM @values
Output:
+-----------+----------------+-----------+
| someValue | expectedOutput | calcValue |
+-----------+----------------+-----------+
| 50,99 | 50 | 50 |
| 53,58 | 50 | 50 |
| 55,23 | 55 | 55 |
| 55 | 55 | 55 |
| 57,58 | 55 | 55 |
+-----------+----------------+-----------+
-- Gianluca Sartori
June 17, 2015 at 6:48 am
Hello again
Thanks for your reply, this is exactly what i need.
Best regards,
Luis Santos
June 17, 2015 at 9:19 am
I don't know that you need negative numbers. Just remember that with using floor, it will return the next lower integer. So if you were to use what spaghettidba put together, you'd end up with
+-----------+-----------+
| someValue | calcValue |
+-----------+-----------+
| -50,99 | -55 |
| -53,58 | -55 |
| -55,23 | -60 |
| -55 | -55 |
| -57,58 | -60 |
+-----------+-----------+
(not saying anything is incorrect - just be sure that's actually what you want)
----------------------------------------------------------------------------------
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?
June 17, 2015 at 9:59 am
Good point, Matt.
A combination of SIGN and ABS will probably help in that case, depending on the logic.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply