May 4, 2017 at 6:06 pm
Here is the FLOOR function I am using , as I know FLOOR will take one paramter but I want to change to ROUND function as it takes two arguments.
how to change this statement o ROUND?
select CAST((FLOOR((E.TotalAmount / COUNT(EE.ID) OVER (PARTITION BY E.ID)) * 100)) / 100 AS decimal(12,2)) AS Employee
from emp E join student EE
on E.ID = EE.ID
May 4, 2017 at 6:49 pm
That's the most non-sensical reason for using a different function I've ever heard. If FLOOR does what you need it to do, then use it. If you need a decimal value instead of an integer, that's a different story.
What are you trying to accomplish? Could you provide some sample inputs and outputs for what you're expecting?
May 4, 2017 at 9:28 pm
floor will round always down so wants to modify with normal math function
for example - 1.229 will round up to 1.23 .
May 4, 2017 at 9:58 pm
ROUND(num,2) doesn't work for you?
May 5, 2017 at 6:32 am
How to tweak this statement - using Round
CAST((FLOOR((E.TotalAmount / COUNT(EE.ID) OVER (PARTITION BY E.ID)) * 100)) / 100 AS decimal(12,2)) AS Employee
May 5, 2017 at 6:49 am
Actually, ROUND uses 3 parameters. The third parameter indicates if it needs to truncate or round.
There's a difference on how FLOOR and ROUND work with negatives.
I explain it further in detail in here: Rounding Tips and Tricks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply