June 9, 2010 at 2:07 pm
Hi. I would like to show AvailableCapacity as a percentage.
I have arrived at a value for AvailableCapacity. How do I show this as a percentage of the total ActivityCentreCapacity?
(p.ActivityCentreCapacity - p.Demand) / p.ActivityCentreCapacity * 100 AS PercentageAvailability
Which is (990 - 720) / 990 * 100 which yields 0 in my SQL but 27.27% on a calculator?
So it must be my code. Scaled down version below.
WITH Proposed AS
(
SELECTCAST(s.Quantity AS INT) AS Quantity,
CASE
WHEN UseMultipleResources = 1 THEN ac.Quantity * ac.Resources
ELSE ac.Quantity
END AS ActivityCentreCapacity,
CAST(mo.Quantity * s.Quantity AS INT) AS Demand,
CASE
WHEN UseMultipleResources = 1 THEN CAST(ac.Quantity * ac.Resources - (mo.Quantity * s.Quantity) AS INT)
ELSE CAST (ac.Quantity - (mo.Quantity * s.Quantity) AS INT)
END AS AvailableCapacity
FROM Structures AS s
INNER JOIN ManufacturingOrders AS mo ON s.Product = mo.Product AND mo.SystemType IN ('P')
INNER JOIN Products ON mo.Product = Products.Product
INNER JOIN Processes AS p ON s.Process = p.Process
INNER JOIN ActivityCentres AS ac ON p.ActivityCentre = ac.ActivityCentre
WHERE s.Type = 'R'AND mo.DueDate BETWEEN '09/06/2010' AND '09/07/2010' AND p.ProcessId = '031'
)
SELECTp.ActivityCentreCapacity,p.Demand,p.AvailableCapacity,
(p.ActivityCentreCapacity - p.Demand) / p.ActivityCentreCapacity * 100 AS PercentageAvailability
FROM Proposed AS p
Anything obvious jump out.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 9, 2010 at 2:14 pm
If I remove CAST all is OK.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 9, 2010 at 2:16 pm
You are almost certainly dealing with INTEGER arithmatic. The data fields you are referring to are most likely INTEGER fields. When arithmatic is performed on two integers, an integer is returned. Since (990 - 720)/990 = 0.27, SQL Server is rounding that to 0. 0 * 100 is, of course, 0.
To exemplify this, try running:
SELECT (990 - 720)/990 * 100
And you'll get zero.
Now, run:
SELECT (990 - 720)/990.0 * 100
And you'll get the correct answer.
Notice the only difference is the 990.0 instead of 990. The DECIMAL constant forces it to evaluate the division as a DECIMAL, and return such. This is exactly what you want to do in your query.
It should be fixed by a single cast:
(p.ActivityCentreCapacity - p.Demand) / CAST(p.ActivityCentreCapacity AS DECIMAL(14,4)) * 100 AS PercentageAvailability
I picked DECIMAL(14,4) arbitrarily, but pick whatever precision you want.
All the other numbers can remain integers. An integer minus and integer is always an integer, so that's fine. An integer divided by a decimal will return a decimal. Then, multiplying that decimal by an integer will again return a decimal.
Hope the helps.
--J
June 9, 2010 at 2:18 pm
It works without the CAST because Demand remains a decimal. The same rules apply. An INTEGER minus a DECIMAL is a DECIMAL. DECIMAL divided by INTEGER is still DECIMAL... et cetera.
Essentially, as long as one of the factors in your division is a DECIMAL data type, it will work. If they are all integers, truncation occurs.
--J
June 10, 2010 at 12:51 am
Thanks for posting.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply