Rounding Up

Problem: User wants his report to round UP to 2 decimal places, he does not want conventional rounding (rounding up or down depending on whether the 3rd decimal place is 0-4 or 5-9)

Solution: Round Function

DECLARE @a TABLE (val DEC(10,6))
INSERT INTO @a VALUES (1.098765432),(8.89)

SELECT
a.val AS original_value,
truncate_two_places.a AS truncated_to_two_decimal_places,
match.a AS truncated_equals_original,
rounded.a AS rounded_up_value

FROM @a a
OUTER APPLY (SELECT ROUND(val,2,1) a) truncate_two_places
OUTER APPLY (SELECT CASE WHEN truncate_two_places.a = a.val THEN 1 ELSE 0 END a) match
OUTER APPLY (SELECT CAST(CASE WHEN match.a=1 THEN truncate_two_places.a ELSE truncate_two_places.a + 0.01 END AS DEC(10,2)) a) rounded

Solution 2: Ceiling Function

DECLARE @a TABLE (val DEC(10,6))
INSERT INTO @a VALUES (1.098765432),(8.89)

SELECT
a.val as original_value,
multiply_by_100.a as mulitply_by_100,
round_up.a as use_ceiling,
CAST(div_by_100.a as dec(3,2)) as final_value

FROM @a a
OUTER APPLY (select a.val*100 a) multiply_by_100
OUTER APPLY (select CEILING(multiply_by_100.a) a) round_up
OUTER APPLY (select round_up.a/100 a) div_by_100

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: