Rounding Down in SQL Select Results

I am trying to round this data, but can't find a good resource on this. If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.

ROUND(DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate -SUM(ISNULL(request_1.request_duration,'0')), .5)

Any help would be greatly appreciated. A link to a good reference on rounding would help too.

Thanks in advance!!!

To start with, when your "rounding" a number, it's usally to the whole number. So 15.6 wouldnt become 15.5, it'd become 16. Thats the way rounding functions are going to work.

Your talking about "aproximation" if your trying to take it to a decimal point. Here's a great resource.

It's got a few good examples, and a launchboard for other functions that may work for you.

http://msdn2.microsoft.com/en-us/library/ms175003.aspx

|||

Thanks for the article. Very informative.

It doesn't look like I can do this with ROUND, FLOOR or CEILING.

Is what I am trying to do even possible?

|||

are your decimals only one point so .5, .6, .7, .8, .9 (no .52345) if you only have those decimals to work with maybe you could hardcode it so if you get a result of 15.6 it there would be a line of 15.6 = 15.5.

I know this is kind of vague but I hope this helps.

|||

Here is the full select statement. Sometimes they will be more than one decimal point out.

SELECT employee.emp_id,ROUND(DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate -SUM(ISNULL(request_1.request_duration,'0')), 1)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

|||

You could try something logically like below:

If floor($inVar) = (round($inVar) - 1)

then

$outVar = (floor($inVar) + 0.5)

else

$outVar = floor($inVar)

That in SQL sauce might be good ;)

|||Is that something I do in the Select statement or in the code in the page?|||

dprichard:

> If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.

So this means you round down to the nearest half or integer?

You might try this, in pseudo-code:

v_h = FLOOR(v * 2) / 2

HTH. -LV

|||

Some comments about leveraging these technics with rounding.

This is the general form (where FLOOR might be CEILING or ROUND depending on needs). We need to approximate to a boundary given as a fraction of the integer. In instance to halves (1/2):

v_h = FLOOR(v * 2) / 2

The idea is we take the reverse of the fraction (1/2 -> 2) and: 1) we multiply by it so that our boundaries match to integers; 2) we apply the rounding; 3) we divide to scale back.

The general function is:

function floorF(v, f) { return floor(v * f) / f; }

Then you can call it asfloorF(v, 100) and you get your number rounded to the cents...

HTH. -LV