Rounding Challenges–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Rounding is an interesting activity, and one that I think people sometimes don’t pay enough attention to. Recently I saw a problem that intrigued me and I spent a few minutes looking at the issues.

Let’s suppose you have data like this for pricing.

0.3

0.7

1.2

1.6

How would you round this data? If you use a simple ROUND(n,0) function, you get this:

0.0

1.0

1.2

2.0

Now, that’s fine, but what if you want this:

0.3

1.0

1.2

2.0

That’s a bit more challenging. I’ll leave the solution out, but note that if I ROUND(n, 1), I get this.

0.3

0.7

1.2

1.6

That’s not quite right either. ROUND() is using the number of decimals I’ve given it, but in some cases, I might want to round up. In this case, I only round up when we’re at .5 or higher, but don’t round down. In those cases, I need to limit rounding. This could be done in a WHERE clause, or with a function (be really careful of scalar functions).

The important part is knowing that I really want this:

ROUND(0.3, 1)

ROUND(0.7, 0)

ROUND(1.2, 1)

ROUND(1.6, 0)

How I get these is up to me, and there are a few ways, but really I want to be sure that I understand how ROUND() works and then apply it appropriately for my situation.

SQLNewBlogger

This is a quick one, literally about 6 minutes to write. About a minute of that was playing with formatting. Understanding functions, and knowing how they affect data is important to show your knowledge.

Also, make sure that you know how to solve something like this if you write about it. I’d encourage you to write the solution as well as remember it. Someone might ask you in an interview Winking smile

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

4 Responses to Rounding Challenges–#SQLNewBlogger

  1. Solomon says:

    Hey Steve. The values for the “what if you want this” part don’t seem consistent. Shouldn’t the 2nd “1.0” really be “1.2”, based on the ROUND statements shown in the “I really want this” part? I used the following to make sure:

    DECLARE @T TABLE (Col1 DECIMAL(19, 4) NOT NULL);
    INSERT INTO @T (Col1) VALUES (0.3), (0.7), (1.2), (1.6);

    SELECT t.[Col1], ROUND(t.[Col1], 0) AS [Rnd0], ROUND(t.[Col1], 1) AS [Rnd1]
    FROM @T t;

  2. Hi, should it works (using @Solomon’s code)?

    SELECT CASE WHEN ROUND(T.Col1, 0) < T.Col1 THEN T.Col1 ELSE ROUND(T.Col1, 0) END
    FROM @T T;

    Bye

Comments are closed.