Rounding Down query

Question

Post reply

Rounding Down query

rdg

Mr or Mrs. 500

Points: 516
More actions
July 8, 2004 at 3:46 am

#108871

Hi
This may sound like a very simple question, but I have been unable to find an easy answer and the one I have come up with may be over complicated!

I have a need to round a figure down to 2 decimal places regardless of the value of the figures after the second decimal. For example 12.3456 needs to be shown as 12.34.
The reason for this is that when working with sales and discounts, our users like to see the following:
original sale value
sale value after discount
discount value

All figures must be to 2 decimal places.
Example
item price = 8.50
qty sold = 1
disc applied = 15%
disc value = 1.275
sale after disc = 7.225

Normal rounding would make the last two values 1.28 and 7.23 - which when added together comes to more than the original sale value.

In my complicated little world, I have come up with the following (see last column - [Correct Calculated Value]). Surely there must be a simpler way?

DECLARE @Disc DECIMAL(10,2)
DECLARE @Qty INT
DECLARE @Price DECIMAL(10,2)
SET @Disc = 0.15
SET @Qty = 1
SET @Price = 8.50
SELECT (@Qty * @Price) AS [Sale Value Pre Discount]
      ,(@Qty * @Price) * @Disc AS [Discount Value]
      ,(@Qty * @Price) - ((@Qty * @Price) * @Disc) AS [Sale Value Post Discount]
      ,ROUND((@Qty * @Price) - ((@Qty * @Price) * @Disc), 2) AS [Rounded Sale Value Post Discount]
      ,ROUND((@Qty * @Price) * @Disc,2) AS [Rounded Discount Value]
      ,ROUND((@Qty * @Price) - ((@Qty * @Price) * @Disc), 2) + ROUND((@Qty * @Price) * @Disc,2) AS [Incorrect Calculated Value]
      ,ROUND(
            (@Qty * @Price) - ((@Qty * @Price) * @Disc), 2)
            + CAST(ROUND(
                  CAST((@Qty * @Price) * @Disc AS INT)
                  + CAST(CAST(((@Qty * @Price) * @Disc) * 100 AS INT) % 100 AS DECIMAL(10,2)) /100
            , 2) AS DECIMAL(10,2)) AS [Correct Calculated Value]

Any ideas?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Here's one inelegant way:

DECLARE @val DECIMAL(18,5)

SET @val = 7.225

SELECT CAST(LEFT(@Val, CHARINDEX('.', @val) - 1) AS VARCHAR(10)) + '.'

+ SUBSTRING(CAST(@Val AS VARCHAR(10)), CHARINDEX('.', @val) + 1, 2)

I'm sure that someone can come up with a better solution. You could probably turn the above into a function as well....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

rdg Mr or Mrs. 500 Points: 516 More actions · Answer 2

Thanks AJ Ahrens

There doesn't seem to be a simple solution!

I'll have a look at this and see about a function.

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 3

Just remember...that consumer laws in most jurisdictions would expect any rounding to be in favour of the customer. A discount of 15% needs in practice to mean 15% or better....not 14.9% nor 14.8%....nor 13%.

Calculate the discount, round it in favour of the customer...and the new sale price is the original price minus the discount.

You're complicating things by calculating a percentage 2 ways....ie 1 for the discount @ 15%...and 2 for the sale price @ 85%....don't...just calc the discount in percentage terms....and then do simple maths afterwards.

rdg Mr or Mrs. 500 Points: 516 More actions · Answer 4

Hi Andrew

Thanks for that response - I fully understand and agree with your statements.

My problem, however, and perhaps I didn't really make it clear, is that the software suppliers update the database with the unrounded figures which I then have to present to two decimal places.

In other words, they would show:

disc value = 1.275

sale after disc = 7.225

I guess I could round up the disc value, find the original price of the product and deduct the rounded discount - that's certainly one way of getting round the problem.

Still doesn't actually answer the "is there an easy way to round down" question though!

Thanks again.

Ken Højrup SSC Rookie Points: 31 More actions · Answer 5

Hi,

Would something simple like this work:

select (floor(<value>*100)/100) from <table> ??

in some unmentioned databases there is a mathematical function called trunc ...

Would be nice here too

rdg Mr or Mrs. 500 Points: 516 More actions · Answer 6

Thanks Ken

Much better than my rather convoluted calculation!

george@esser.ca SSC Enthusiast Points: 174 More actions · Answer 7

You want to truncate the value to 2 decimal places

Multiply by 100, convert to an integer then convert to a Numeric(18,2) (or currency) and divide by 100

CONVERT(Numeric(18,2), CONVERT(int, <value> * 100))/100

George

William H. Latimer Grasshopper Points: 11 More actions · Answer 8

To either round or truncate to N decimal places, use the formula (or write a UDF)

(10 ^ 0-N) * floor(<value> * 10 ^ N) to truncate or

(10 ^ 0-N) * floor(0.5 + <value> * 10 ^ N) to round