SQL Select Round Down to Half Percent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Select Round Down to Half Percent

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 11:25:01

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!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-23 : 11:45:43

[code]

select
x,
case
when x < floor(x)+.5
then floor(x)
else floor(x)+.5
end
from
(
select x= 15.4 union all
select 15.6 union all
select 15.0 union all
select 15.9
) a

[/code]

CODO ERGO SUM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 11:53:13

SELECT Floor(13.6/0.5) * 0.5 , Floor(13.4/0.5) * 0.5

Srinika

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 13:35:42

Okay, please excuse my total ignorance. I am kinda new to SQL and .NET.

Here is my whole query:


SELECT     employee.emp_id, (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_name
FROM         employee LEFT OUTER JOIN
                      request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
                      department ON employee.emp_department = department.department_id INNER JOIN
                      location ON department.department_location = location.location_id
GROUP 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_name
ORDER BY location.location_name, department.department_name, employee.emp_lname

I am trying to make it so the result for daysleft shows 15.0 if it is 15.0 - 15.4 and show 15.5 if it is 15.5-15.9

Again, I appreciate the resoponses, but I am kinda new and am not sure how this would work into my select statement.

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 13:42:36

Sorry, disreguard the select statement above. This is the full one:


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_name
FROM         employee LEFT OUTER JOIN
                      request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
                      department ON employee.emp_department = department.department_id INNER JOIN
                      location ON department.department_location = location.location_id
GROUP 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_name
ORDER BY location.location_name, department.department_name, employee.emp_lname

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 14:22:19

Change


SUM(ISNULL(request_1.request_duration, '0')), 1) 

to

Floor( SUM(ISNULL(request_1.request_duration, 0)), 1)/0.5) * 0.5

Srinika

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 14:54:40

Tried this and it is telling me "The floor function requires 1 arguement"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 15:06:46

Sorry,

Floor( SUM(ISNULL(request_1.request_duration, 0))/0.5) * 0.5

Srinika

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 15:16:22

I really appreciate your help. Now I am getting a different error:

"Unable to parse query text."


SELECT     employee.emp_id, ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) 
                      * employee.emp_accrual_rate - Floor( SUM(ISNULL(request_1.request_duration, 0))/0.5) * 0.5 AS daysleft, 
                      employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, 
                      location.location_name
FROM         employee LEFT OUTER JOIN
                      request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
                      department ON employee.emp_department = department.department_id INNER JOIN
                      location ON department.department_location = location.location_id
GROUP 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_name
ORDER BY location.location_name, department.department_name, employee.emp_lname

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-23 : 15:29:13

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

also note that

The round function requires 2 to 3 arguments.

Srinika

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-23 : 16:41:54

k, I give up... Researched this all day and can't find an answer. Query works except for the FLOOR statement. Once I put that in I get the parse error.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-23 : 17:44:04

Hey! Theres no giving up at sqlteam...

I dont know if this query gives you the desired resultset, but syntax-wise your second query is missing the 2nd argument for the ROUND function:


SELECT  employee.emp_id, 
        ROUND   (	DATEDIFF(mm, e.emp_begin_accrual, GETDATE()) * e.emp_accrual_rate - (Floor(SUM(ISNULL(r.request_duration, 0))/0.5) * 0.5)
			, 1
		) AS daysleft,
        e.emp_lname + ', ' + e.emp_fname + ' ' + e.emp_minitial + '.' AS emp_name, 
        d.department_name, 
        l.location_name
FROM    employee e
LEFT 
JOIN    request r ON 
        e.emp_id = r.emp_id 
INNER 
JOIN    department d ON 
        e.emp_department = d.department_id 
INNER 
JOIN    location l ON 
        d.department_location = l.location_id
GROUP 
BY      e.emp_id, e.emp_begin_accrual, e.emp_accrual_rate, e.emp_fname, e.emp_minitial, 
        e.emp_lname, d.department_name, l.location_name
ORDER 
BY      l.location_name, d.department_name, e.emp_lname

Nathan Skerl

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-08-24 : 08:09:18

Thanks for the response Nathan. I am back to getting the same response as before, but no more error. I am going to have to find a different way inputting the data to get the results I want. Thank you everyone for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:16:20

Try to select only the rounding/floor part, and disregarding everything else. That might give you a start where to search for the error.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:18:37

Are you using MS SQL Server?

Peter Larsson
Helsingborg, Sweden

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-08-26 : 07:00:55

[code]
declare @x money, @y money

select @x = 15.4, @y = 15.6

select floor(2* @x)/2, floor(2*@y)/2
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:32:58

select (cast(10 * @x as int)-cast(10 * @x as int)% 5 ) / 10.0

Peter Larsson
Helsingborg, Sweden