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?
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?
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)
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
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);
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
Ah good catch james, I missed up round up part from the OP.
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.