Roounding to nearest .25 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Roounding to nearest .25

Author

Topic

CXXXV
Starting Member

12 Posts

Posted - 2009-10-22 : 16:35:13

Using the following statement:

 CAST(DATEDIFF(MINUTE, TIME_IN, TIME_OUT)/60.00 AS decimal(10,2)) as STRAIGHT ,
round((DATEDIFF(MINUTE, TIME_IN, TIME_OUT)/60.00 * 4), 0)/4 as ROUNDED
FROM HOURS

yields the following results:

STRAIGHT ROUNDED
0.08 0.00000
4.33 4.25000
0.03 0.00000
9.57 9.50000
4.95 5.00000

What I need are rounded results to the nearest .25

DESIRED
0.25
4.50
0.25
10.00
5.00 <----THIS ONE WORKED

Some where in my statement I have some thing wrong but I can't figure it out.

Any help will be appreciated.

Tx.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-22 : 17:17:48

So you really mean to round UP to the nearest .25 right?

Try this:

CAST(DATEDIFF(MINUTE, TIME_IN, TIME_OUT)/60.00 AS decimal(10,2)) as STRAIGHT ,
ceiling((DATEDIFF(MINUTE, TIME_IN, TIME_OUT)/60.00 * 4))/4 as ROUNDED
FROM HOURS


select ceiling(i * 4)/4
from   (
select .08 i union all select .33 union all select .03 union all select .57 union all select .95
) d

output:

-----------
0.250000
0.500000
0.250000
0.750000
1.000000

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources