How do you Round it of in multiples of 0.5-CodePudding

first time posting

working on this project in BigQuery Where I want to round of the weight slab in multiples of 0.5KG

For example

0.4KG round it of to 0.5KG
2.1KG THEN round it of to 2.5KG

even if it is 50 or 100 grams above than the current weight slab than I want it to round it of to the next weight slab

I tried WHEN WKG.Weight_KG<=0.5 THEN WKG.Weight_KG=0.5 but the output comes in boolean format

even tried this WHEN WKG.Weight_KG<=0.5 THEN ROUND(WKG.Weight_KG/ .5,0) * .5 but few of the numbers were rounded of to 0.0 instead of 0.5

CodePudding user response：

This should work

case when round(weight * 10) % 10 = 0 then round(weight) else case when round(weight * 10) % 10 >= 5 then round(weight)   1 else round(weight)   0.5 end end as weight

CodePudding user response：

You can use

TRUNC(value)   CEIL(MOD(value, 1.0) / 0.5) * 0.5

For instance,

SELECT value, TRUNC(value)   CEIL(MOD(value, 1.0) / 0.5) * 0.5 AS rounded_up
FROM UNNEST(generate_array(CAST(-0.6 AS NUMERIC), 2.6, 0.1)) AS value

returns

value  rounded_up
-0.6   -0.5
-0.5   -0.5
-0.4   0
-0.3   0
-0.2   0
-0.1   0
0      0
0.1    0.5
0.2    0.5
0.3    0.5
0.4    0.5
0.5    0.5
0.6    1
0.7    1
0.8    1
0.9    1
1      1
1.1    1.5
1.2    1.5
1.3    1.5
1.4    1.5
1.5    1.5
1.6    2
1.7    2
1.8    2
1.9    2
2      2
2.1    2.5
2.2    2.5
2.3    2.5
2.4    2.5
2.5    2.5
2.6    3