Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Problem with Round Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 23, 2014 10:57 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, July 23, 2014 1:31 PM Points: 3, Visits: 26
 I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values ​​for the percentage (also using round).IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #RedondeoCreate Table #Redondeo (Orden int Identity(1,1),Valores money)Insert into #RedondeoSelect 71374.24 Union Select 16455.92 Union Select 56454.20 Union Select 9495.18 Union Select 23894.20 Select *,Round(Valores*.21,2) as IVAfrom #RedondeoCompute sum(Valores),sum(Round(Valores*.21,2))RESULT = 177673.74; 37311.48Select sum(Valores),round(sum(Valores *.21),2) as IVAfrom #RedondeoRESULT: 177673.74; 37311.49
Post #1595599
 Posted Wednesday, July 23, 2014 11:23 AM
 SSCrazy Eights Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 8,233, Visits: 17,809
 The problem is that one query is summing the values before rounding and the other one is summing the values after rounding. You need to define which option do you want.This should make it clear.`Select sum(Valores), sum(round(Valores *.21,2)) as IVA, --Round then sum Round(sum(Valores*.21),2) AS IVA2, --Sum then round sum(Valores*.21) AS IvaSinRedondear --Just Sumfrom #Redondeo` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2
Post #1595611
 Posted Wednesday, July 23, 2014 12:20 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, July 23, 2014 1:31 PM Points: 3, Visits: 26
 Thanks Luis, I'll try your suggestions
Post #1595650
 Posted Wednesday, July 23, 2014 1:31 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, July 23, 2014 1:31 PM Points: 3, Visits: 26
 To clarify the problem. The initial calculation is: Round (177673.74 * 0.21,2) = 37311.49 Then I need to make the opening of each item and the corresponding calculation, which must match the sum to the initial calculation, but it gives 0.01 difference:Ítems; Valores; Round (Valores*0.21,2)Item 1; 9,495.18; 1,993.99 Item 2 16,455.92 3,455.74 Item 3 23,894.20 5,017.78 Item 4 56,454.20 11,855.38 Item 5 71,374.24 14,988.59 37,311.48 But I think there is no solution can only be set in any of the items the difference
Post #1595689
 Posted Wednesday, July 23, 2014 2:07 PM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 3:27 PM Points: 3,321, Visits: 32,649
 just a thought...but appears to work, but maybe I am not entirely clear on the issue`IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #RedondeoCREATE TABLE #Redondeo ( Orden INT Identity(1, 1) , Valores MONEY )INSERT INTO #RedondeoSELECT 71374.24 UNIONSELECT 16455.92 UNIONSELECT 56454.20 UNIONSELECT 9495.18 UNIONSELECT 23894.20SELECT Orden , Valores ,round (Valores * 0.21,2) as rndFROM #RedondeoSELECT Orden , Valores ,round (Valores * 0.21,2) as rndinto #testsumFROM #RedondeoSELECT SUM(rnd) AS sumbyrowFROM #testsumSELECT SUM(ROUND(Valores * 0.21, 2)) AS sumrndFROM #Redondeo` ______________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day
Post #1595705
 Posted Friday, July 25, 2014 3:04 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 5:57 PM Points: 7,507, Visits: 17,963
 mballico (7/23/2014)To clarify the problem. The initial calculation is: Round (177673.74 * 0.21,2) = 37311.49 Then I need to make the opening of each item and the corresponding calculation, which must match the sum to the initial calculation, but it gives 0.01 difference:Ítems; Valores; Round (Valores*0.21,2)Item 1; 9,495.18; 1,993.99 Item 2 16,455.92 3,455.74 Item 3 23,894.20 5,017.78 Item 4 56,454.20 11,855.38 Item 5 71,374.24 14,988.59 37,311.48 But I think there is no solution can only be set in any of the items the differenceRounding errors will occur in any scenario like this. It's not that SQL Server's ROUND() function has an issue, the use of rounding lends itself to rounding errors.http://www.investopedia.com/terms/r/rounding-error.asp ----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1596423
 Posted Wednesday, July 30, 2014 6:53 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, October 3, 2016 5:19 AM Points: 1,144, Visits: 3,432

`CREATE TABLE #t (Valores MONEY);GOINSERT INTO #t SELECT 1.1;GO 6SELECT	SUM(ROUND(Valores, 0)) AS SumRound,	ROUND(SUM(Valores), 0) AS RoundSumFROM	#t;DROP TABLE #t;`

The SQL Guy @ blogspot

@SeanPearceSQL