srejbi.info^experimental

though the latest (0.9.x) versions of Redmine 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:

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;

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:

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

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.

id	fixed_version_id	estimated_hours	done_ratio	time_spent	rem_est_est	rem_est_done	subject
1	NULL	80	0	NULL	80	NULL	an unstarted issue with estimate
2	NULL	NULL	0	NULL	NULL	NULL	unstarted issue without estimate
3	3	5	100	4.70000004023314	0	0	completed issue 1
6	3	8	100	12.1599999666214	0	0	completed issue 2
4	4	4	30	1.30000002682209	2.8	3.033290062584	issue progressing slightly worse than estimated
5	4	6	10	1.89999997615814	5.4	17.099999785423	issue progressing much worse than estimated
7	4	16	60	7.95000000298023	6.4	5.3002650019869	issue progressing better than estimated
8	4	3	90	1.5	0.3	0.16665	issue progressing much better than estimated

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… :)