SQLTeam.com | Weblogs | Forums

Rounding to nearest 0.25


#1

Hi, I need to round up to the nearest 0.25 e.g.

0.15 = 0.25
0.25 = 0.25
0.27 = 0.50
0.49 = 0.50
0.55 = 0.75
1.10 = 1.25
1.27 = 1.50
etc, etc. What parameters would I need using the ROUND() function?


#2

Try this

SELECT	A.B,
		ROUND(A.B * 4,0) / 4, --0.25
		ROUND(A.B * 2,0) / 2 --0.5
FROM	(
			VALUES (0.15),(0.25),(0.27),(0.49),(0.55),(1.10),(1.27)
		) AS A(B)

#3

This also would be helpful

SELECT    col1, case 
                    when col1 - cast(col1 as int) > 0.01 and  col1 - cast(col1 as int) <= 0.25 
                        then 0.25 
                    when col1 - cast(col1 as int) > 0.25 and  col1 - cast(col1 as int) <= 0.50
                        then 0.50 
                    when col1 - cast(col1 as int) > 0.50 and  col1 - cast(col1 as int) <= 0.75
                        then 0.75
                    when col1 - cast(col1 as int) > 0.75 and  col1 - cast(col1 as int) <= 0.99
                        then 1.00
                end

#4

ROUND function lets you ROUND to the nearest boundary value or truncate to the nearest boundary value depending on the third parameter. Since you want to ROUND UP always, CEILING function would be the approprate choice.

SELECT B,CEILING(A.B*4)/4E AS B_RoundedUP
FROM	(
	    	VALUES (0.15),(0.25),(0.27),(0.49),(0.55),(1.10),(1.27)
	) AS A(B);

#5

SELECT B,CAST(SUBSTRING(B,1,CHARINDEX('.',B))AS VARCHAR)+ CAST(CASE WHEN DIG BETWEEN 1 AND 25 THEN 25
WHEN DIG BETWEEN 26 AND 50 THEN 50
WHEN DIG BETWEEN 51 AND 75 THEN 75
ELSE DIG END AS VARCHAR)
FROM(
select B,SUBSTRING(B,CHARINDEX('.',B)+1,LEN(B))DIG from #tmp1)A


#6

Ah good catch james, I missed up round up part from the OP.


#7

I have been bogged down during the last couple of days and haven't had an opportunity to try these out, but thanks very much for your help.
Nev.