July 8, 2004 at 3:46 am
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
July 8, 2004 at 5:00 am
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
July 8, 2004 at 5:35 am
Thanks AJ Ahrens
There doesn't seem to be a simple solution!
I'll have a look at this and see about a function.
July 9, 2004 at 2:46 am
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.
July 9, 2004 at 3:00 am
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.
July 9, 2004 at 3:31 am
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
July 9, 2004 at 5:40 am
Thanks Ken
Much better than my rather convoluted calculation!
July 9, 2004 at 10:17 am
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
July 13, 2004 at 5:48 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply