SQl Multiplication to 2 digits doesn't add up to the exact decimal
-
Hey Guys,
I have this situation where I output to a csv which uploads to another system and the system check between both the summary amount and split amount. Even if it is off by .01 it will throw an error and configured to take only 2 digits. The cast as decimal (18,2) is rounding the numbers in some cases and the rounding function may not work. However the natural multiplication always adds up to the right sum but it goes to 3 decimals which is not what I want . How can I make it fixed to 2 decimals and make sure the splits are always right ?
Thanks,
V
Code Below :
select 0.3*149.99
union all
select 0.2*149.99
union all
select 0.1*149.99
union all
select 0.1*149.99
union all
select 0.2*149.99
union all
select 0.1*149.99select cast((0.3*149.99) as decimal(18,2))
union all
select cast((0.2*149.99) as decimal(18,2))
union all
select cast((0.1*149.99) as decimal(18,2))
union all
select cast((0.1*149.99) as decimal(18,2))
union all
select cast((0.2*149.99) as decimal(18,2))
union all
select cast((0.1*149.99) as decimal(18,2))
-
In this instance, which results would you deem as 'right'? I'm assuming that you want want of those results to round down by 0.01, but which one and what is the rule you want to follow?
One bullet-proof way of achieving what you want is to calculate the final split as
(Total Amount) - (Sum of other split amounts)
But you'd still need to define how 'final' is determined.The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I would say the first sql results splits are right and it should sum to 149.99 and yes I would like them to round and the bullet proof method is correct "(Total Amount) - (Sum of other split amounts)". But does that mean I have to run an update on rounding down separately ?
-
It looks like you're trying to make the sum of rounded numbers equal the sum of unrounded numbers. That won't work. That's why poll results often say, "Totals may not add to 100% due to rounding."
Your business rules will have to be changed to allow rounding mismatches. Or the rules will have to be modified to tell you which one of the split results you want to be adjusted - adjusted inaccurately in my opinion - to make the rounded sum come out "correctly".
Time to go talk to the Finance or Billing Department (or whoever owns this process) to see how they want you to proceed. It's no longer an IT decision.
-
Here is some SQL which does this, on the assumption that the value with the highest percentage split is the one to be 'corrected'.
DECLARE @SplitPc TABLE (SplitPercentage DECIMAL(18, 6));INSERT @SplitPc
(
SplitPercentage
)
VALUES
(0.3)
,(0.2)
,(0.1)
,(0.1)
,(0.2)
,(0.1);DECLARE @Price DECIMAL(18, 6) = 149.99;
WITH RoundedSplit
AS
(
SELECT
ro = ROW_NUMBER() OVER (ORDER BY sp.SplitPercentage DESC)
, Price = @Price
, SplitPrice = ROUND(@Price * sp.SplitPercentage, 2)
FROM @SplitPc sp
)
SELECT
RoundedSplit.ro
,RoundedSplit.Price
,RoundedSplit.SplitPrice
, SplitPriceCorrected = (CASE
WHEN RoundedSplit.ro = 1 THEN
(
SELECT TotalSum = MAX(rs2.Price) - SUM(rs2.SplitPrice)
FROM RoundedSplit rs2
WHERE rs2.ro <> 1
)
ELSE
RoundedSplit.SplitPrice
END
)
FROM RoundedSplit;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks Phil ! Pretty close to what I was expecting, just updating it after allocations run to take care of the .001's.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply