get time estimates from redmine

though the latest (0.9.x) versions of <a href="http://www.redmine.org">Redmine</a> definitely has improved issues display, still some columns are missing from the view, such as the time spent already and projections of the total time (or time remaining) based on the original estimate, time inputted already and the completion ratio. below is my dirty SQL workaround for getting these base numbers out for simple maths: <pre> <code class="sql"> SELECT distinct i.id, fixed_version_id, estimated_hours, done_ratio, SUM(te.hours) AS time_spent, subject FROM issues i LEFT OUTER JOIN time_entries te ON i.id = te.issue_id WHERE i.project_id = ${project} GROUP BY i.id ORDER BY fixed_version_id ASC, id ASC; </code> </pre> where ${project} is your selected project; _ or also one can remove condition, add ordering by i.project_id and see all projects (also could put any other columns there)_ or do already the simple math for doing linear projections of the remaining time estimate based on the original or on the already spent time: <pre> <code class="sql"> SELECT DISTINCT i.id, fixed_version_id, estimated_hours, done_ratio, SUM(te.hours) AS time_spent, (CASE done_ratio WHEN 100 THEN 0 ELSE (estimated_hours*(100-done_ratio)/100) END) AS rem_est_est, (CASE done_ratio WHEN 100 THEN 0 ELSE (SUM(te.hours)*(100/done_ratio)-SUM(te.hours)) END) AS rem_est_done, subject FROM issues i LEFT OUTER JOIN time_entries te ON i.id = te.issue_id WHERE i.project_id = ${project} GROUP BY i.id ORDER BY fixed_version_id ASC, id ASC </code> </pre> these SQL statements were tested on MySQL, but supposed to work without modification on PostgreSQ. tested with redmine versions 0.8 – 0.9.2 this would result a table like this. <table style="border:1px solid gray;"> <tr> <td>id</td> <td>fixed_version_id</td> <td>estimated_hours</td> <td>done_ratio</td> <td>time_spent</td> <td>rem_est_est</td> <td>rem_est_done</td> <td>subject</td> </tr> <tr> <td>1</td> <td>NULL</td> <td>80</td> <td>0</td> <td>NULL</td> <td>80</td> <td>NULL</td> <td>an unstarted issue with estimate</td> </tr> <tr> <td>2</td> <td>NULL</td> <td>NULL</td> <td>0</td> <td>NULL</td> <td>NULL</td> <td>NULL</td> <td>unstarted issue without estimate</td> </tr> <tr> <td>3</td> <td>3</td> <td>5</td> <td>100</td> <td>4.70000004023314</td> <td>0</td> <td>0</td> <td>completed issue 1</td> </tr> <tr> <td>6</td> <td>3</td> <td>8</td> <td>100</td> <td>12.1599999666214</td> <td>0</td> <td>0</td> <td>completed issue 2</td> </tr> <tr> <td>4</td> <td>4</td> <td>4</td> <td>30</td> <td>1.30000002682209</td> <td>2.8</td> <td>3.033290062584</td> <td>issue progressing slightly worse than estimated</td> </tr> <tr> <td>5</td> <td>4</td> <td>6</td> <td>10</td> <td>1.89999997615814</td> <td>5.4</td> <td>17.099999785423</td> <td>issue progressing much worse than estimated</td> </tr> <tr> <td>7</td> <td>4</td> <td>16</td> <td>60</td> <td>7.95000000298023</td> <td>6.4</td> <td>5.3002650019869</td> <td>issue progressing better than estimated</td> </tr> <tr> <td>8</td> <td>4</td> <td>3</td> <td>90</td> <td>1.5</td> <td>0.3</td> <td>0.16665</td> <td>issue progressing much better than estimated</td> </tr> </table> how realistic are these figures? i do not know — it all comes down to the issue specs. the high resolution and precisely detailed issue specification is the key factor to increase the precision of initial and ongoing estimates (completion ratio), generally speaking. however there could be an entire post about it… anyway, back to the original topic; the above SQL is good enough for me to keep tracking how my – often lousily specified issues – keep progressing – as the specs are getting refined as well as work is being done on the issues – so i can have a better idea of where the project will end up (in terms of time spent) than if i just look at the remaining (original) estimate. however, one can think of many other things to do here, eg. interpolate time entries to get another projected total time per issue / project, or calculate projected estimate for non-started tasks based on the original estimate and the precision of original estimates in the project (based on closed tasks). i have no need for that stuff right now, and no time to play with it just for fun (though would be interesting to see), maybe another time… :) 2010-02-10T11:47:41Z quick-and-dirty solution to get projected estimates from redmine (based on completion ratio and work done already) 2 true get time estimates from redmine

2010-02-10T12:26:41Z

well, i am not a cobbler, actually… just another busy software engineer that wonders every once in a while why his own website looks like crap /if there is a website at all :)/… having my personal page up with <a href="http://wordpress.org">wordpress</a> for some time now, i felt a little less pressure to do anything about it. however, doing plenty of rails stuff for some time now, started to wonder why i should keep running my site on wp and not just create something simple and stupid in rails. so here it goes. <a href="https://dev.mobility.ws:4443/projects/show/yaqrb">project page</a> this is free software, so feel free to grab it if you need something like this. <pre> git clone git://git.mobility.ws/yaqrb.git </pre> 2010-02-05T22:59:11Z as masses confirm, the old say proves to be true. i can confirm too. but - speaking for myself - i am about to fix it... 1 true The Cobbler's Children Have No Shoes

2010-02-06T16:51:10Z