April 21, 2012 at 12:39 pm
I want to calculate ratio to parent for a table named Level1Level2 with this values:
Level1Level2Quantity
A A1 120
A A2 130
A A3 145
A A4 160
B B1 300
B B2 123
In this table the field Quantity is a money type....
I use this query:
WITH sumQty AS (SELECT SUM(Quantity) AS sommaLevel1, Level1
FROM dbo.Level1Level2
GROUP BY Level1)
SELECT l.Level1, l.Level2, l.Quantity / s.sommaLevel1 * 100 AS IncidPerc
FROM dbo.Level1Level2 AS l INNER JOIN
sumQty AS s ON l.Level1 = s.Level1
I obtain this result:
A A1 21,62
A A2 23,42
A A3 26,12
A A4 28,82
B B1 70,92
B B2 29,07
As you can see the sum for Level1=A is not 100 but 99,98.The same for Level1=B....
Is it a method to obtain a better result for RatioToParent, so that the sum is 100?
Thank you
April 21, 2012 at 12:51 pm
antonela (4/21/2012)
I want to calculate ratio to parent for a table named Level1Level2 with this values:Level1Level2Quantity
A A1 120
A A2 130
A A3 145
A A4 160
B B1 300
B B2 123
In this table the field Quantity is a money type....
I use this query:
WITH sumQty AS (SELECT SUM(Quantity) AS sommaLevel1, Level1
FROM dbo.Level1Level2
GROUP BY Level1)
SELECT l.Level1, l.Level2, l.Quantity / s.sommaLevel1 * 100 AS IncidPerc
FROM dbo.Level1Level2 AS l INNER JOIN
sumQty AS s ON l.Level1 = s.Level1
I obtain this result:
A A1 21,62
A A2 23,42
A A3 26,12
A A4 28,82
B B1 70,92
B B2 29,07
As you can see the sum for Level1=A is not 100 but 99,98.The same for Level1=B....
Is it a method to obtain a better result for RatioToParent, so that the sum is 100?
Thank you
The accuracy issue is due to the money datatype which uses 4 decimal places. When the division occurs in the select level1/sommalevel1 returns to 4 places which become 2 places when you multiply by 100. Try casting the data vales to decimal(30,8) or some type to increa the decimal places.
Fitz
April 21, 2012 at 1:06 pm
Thank you.
It functions properly.
April 23, 2012 at 12:32 am
You can also fudge round using this technique if you don't want to mess with your precision:
http://www.sqlservercentral.com/articles/Financial+Rounding/88067/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply