May 30, 2013 at 8:21 pm
Hi everyone,
I have problem with rounding. There is a table
CREATE TABLE [dbo].[Fin3PaymentDisb](
[PaymentID] [bigint] NOT NULL,
[Currency] [char](3) NOT NULL,
[Amount] [money] NOT NULL,
[Rate] [float] NOT NULL,
[CurrencyPay] [char](3) NULL,
[RatePay] [float] NOT NULL,
[AmountPay] AS ([Amount]*[Rate]),
CONSTRAINT [PK_Fin3PaymentDisb] PRIMARY KEY CLUSTERED
with values
AMOUNT = 3875.0000
RATE= 0.17836
AMOUNTPAY = 691.145
And this query returns record
SELECT P.*
FROM Fin3Payment P
INNER JOIN (
SELECT PaymentID, SUM(AmountPay) AS Amount
FROM Fin3PaymentDisb
GROUP BY PaymentID
) AS L ON L.PaymentID = P.PaymentID AND ROUND(Round(L.Amount, 2) - ROUND(P.Amount - P.Offset - P.SetDisc, 2), 2) <> 0
where PaymentAmount = 691, Offset = -0.15 and SetDisc = 0, i.e. 691.145 is rounded to 691.14. But in the query
SELECT ROUND(691.145, 2)
result is 691.15.
What could be the problem here? It runs on MS SQL 2005 Express SP2.
Thanks.
May 30, 2013 at 9:05 pm
The problem you are running into is that you are using float for your rate. This results in the multiplication of Amount * Rate results in a float value. What you see as 691.145 is not actually stored as 691.145 as demonstrated in your post and below:
declare
@PaymentID bigint,
@Currency char(3),
@Amount money,
@Rate float,
@CurrencyPay char(3),
@RatePay float;
select @Amount = 3875.0000, @Rate = 0.17836 -- , @AMOUNTPAY = 691.145
select round(@Amount * @Rate, 2), @Amount * @Rate;
go
declare
@PaymentID bigint,
@Currency char(3),
@Amount money,
@Rate decimal(6,5),
@CurrencyPay char(3),
@RatePay float;
select @Amount = 3875.0000, @Rate = 0.17836 -- , @AMOUNTPAY = 691.145
select round(@Amount * @Rate, 2), @Amount * @Rate;
go
July 10, 2013 at 2:29 am
Thank you Lynn! Your suggestion is working.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply