July 2, 2008 at 5:14 am
Hi. Wonder if someone can help with this as it's now driving me to distraction?
I have a Time-sheet utility that I use to record my development work - i.e. who's it for, how long have I spent on it, the usual; stuff. At the end of the month the utility produces a report giving my activities as percentages of the time spent on individual jobs against the total time for all jobs in the period. A Stored Procedure is used to generate the report data.
The concept is all fairly straightforward however I'm having terrible trouble getting accurate figures. The report Store Procedured totals the number of minutes I've spent on each job and calculates the percentage of that total against the total number of minutes spent on all jobs. I'm converting both the 'Job minutes' and 'total minutes' figures into NUMERIC(7, 2) values in order to get the two figures as non-integer values then using ROUND to get percentages to 2 decimal places. However this is introducing inaccuracies so that I quite often end up with a total % of 99.9 or 100.1 if I add up the percentages for the individual jobs. If I leave the rounding out, the figures are correct however I have to round them somewhere - either in the SP or in the finished report - to make them ledgible and the rounding sooner or later throws the accuracy out.
Can anyone suggest a solution?
Regards,
YaHozna.
July 2, 2008 at 7:38 am
Rounding will cause errors. That's a mathematical fact.
You're dealing with 2 significant digits (after the decimal place), and then rounding them, which means you're only dealing with accuracy of 1 significant digit. That's probably too few for minutes/month.
Assuming a 40-hour average workweek, and a 21-workday average workmonth, you have 10,080 minutes available per month. That means you should be working with 5 significant digits, no more, no less. Change to decimal(10,5), then round to decimal(10,4) after you've done your calculations. If you're off by +/-0.0005, you are still within the precision that makes sense with that level of accuracy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 7:49 am
Education of your user base....
Even with the excellent advice from Gus - enough detail records being rounded off will STILL introduce those occasional "issues", even if you're maintaining enough significant digits in the background. These are just the nature of the beast. If you had 10,000 detail rows with their own percentages, the likelihood that your "display" percentages will ever add up to exactly 100% are going to be slim no matter how good your storage is.
----------------------------------------------------------------------------------
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?
July 2, 2008 at 9:50 am
If you actually need it to add up to exactly 100%, you need to have something go through, add it up, find the error amount, spread that over as many records as possible.
For example, if you have 50 rows, and the total is 100.1%, and your precision is 2-digit, you could take 0.1/50 and subtract that from each row, but it will round again, and might end up with the wrong amount. Or you could have it subtract 0.1 from one row. Variations on that.
But that's "cooking the numbers", and might be worse than leaving the rounding error in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 7:23 am
IMO, a percentage is a calculation, no more. If you want to see the percentage of time you spent on, let's say a specific client over the course of a month, you would not add percentages. You would sum all time spent on that client's projects divided by the sum of the time spent on all projects. Storing percentages in a database is nothing more than a "quick reference" field, and a percentage should never be aggregated.
July 7, 2008 at 10:01 am
Many thanks to those who took the time to respond to my post. And apologies for the lateness of this reply - was enjoying a holiday (yes, they let me out occasionaly).
I'll have a look through the suggestions asap. in the mean time thanks again.
Regards,
YaHozna.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply