June 9, 2015 at 8:55 am
I am a bit confused on the logic..
If we need to calculate the Percentage of Closed Purchase Orders .
Does that mean we need to implement like this ?
SUM(of Purchase Order where Status is Closed) / Total Purchase Orders * 100 ?
June 9, 2015 at 9:43 am
That would be the correct logic.
You'll have to deal with the fact that the number of closed purchase orders and the total number of purchase orders will be integers, so that division will just return 0 (assuming there are more purchase orders than there are closed purchase orders, which seems reasonable :-)). You'd want to cast either the divisor or dividend as a decimal type with appropriate precision and scale (if you just want the number of percentage points as an integer, and don't care about any but the first 2 significant digits, you can also get the same result with integer math by first multiplying the dividend by 10000 and the divisor by 100, and only then doing the division).
On a different note, even though in this case all the operators have the same precedence and they'll be evaluated in the desired order (from left to right), I generally like to use parentheses to clarify anyway (note that the order in which the multiplication and division occur matters).
I hope this helps.
Cheers!
June 9, 2015 at 2:52 pm
Thanks a lot
June 9, 2015 at 2:58 pm
sharonsql2013 (6/9/2015)
I am a bit confused on the logic..If we need to calculate the Percentage of Closed Purchase Orders .
Does that mean we need to implement like this ?
SUM(of Purchase Order where Status is Closed) / Total Purchase Orders * 100 ?
You should be asking that question to whomever requested the information. That person may have a different understanding than we do.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 10, 2015 at 6:13 am
Also watch for the case when there are no closed work orders. This may never happen but it might.
June 10, 2015 at 7:35 am
djj (6/10/2015)
Also watch for the case when there are no closed work orders. This may never happen but it might.
A good point! There are actually a couple such points I overlooked in my initial response.
Elaborating on what djj said, if you are actually using a SUM (say, on a column that records a number of work orders), then if there are no rows where the status is closed, the SUM will return a NULL, which would lead to the result being a NULL.
On the other hand, if (as I think is more likely) you have a table where each row represents a purchase order, then you'll likely be using COUNT, not SUM, and that won't be an issue.
A related issue to watch for is the case where total work orders is 0. Similar to the point djj made, that might never happen, but if the query that gets the percentage of work orders that are closed is allowed to pass in criteria to a WhERE clause, then it might filter out all work orders.
In that case, you'd get a divide by zero error, so you'd want to allow for that (a CASE statement that evaluates total work orders and only performs the division if total work orders is non-zero would be one way).
Cheers!
June 11, 2015 at 3:01 pm
Noted. Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply