Redmine

I use the following query to obtain the information.

select story.id,
       story.subject,
       max(story.estimated_hours) story__estimated,
       sum(subtask.estimated_hours) subtask__estimated,
       sum(te.hours) subtask__spent_time

  from issues story

  join issues subtask
    on subtask.parent_id = story.id

  left
  join (select project_id, 
               issue_id,
               sum(hours) hours
          from time_entries 
         where project_id = 1
         group 
            by project_id, 
               issue_id         
        ) te
    on te.issue_id = subtask.id
   and te.project_id = subtask.project_id

 where story.project_id = 1
   and story.tracker_id = 4

 group 
    by story.id,
       story.subject

+1

Just in case it can help, a quick patch for v1.4.4. Just add the following

      @available_columns.insert index, QueryColumn.new(:total_spent_hours,
        :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
        :default_order => 'desc',
        :caption => :label_overall_spent_time
      )

to the app/models/query.rb file, at line 382 (just after the same code for the spent_hours column). Restart apache2 and it should add the desired column

Warning : it really is just a quick hack, if you look at the :sortable entry, the SQL code isn't what it should be. It works for issue list, but it may fails miserably for some other part of Redmine... --Update-- : well, I should have guessed, when you order the list by this overall spent time, it fails as it sorts by the non-overall spent time...

HTH.

As a side note : that's weird, with my redmine.org's account, I can change this issue status and its assignation ! Have I missed something ??

+1

i have this problem in redmine 2.0.3

+1

i use idea of Sylvain Langlade with query of Victor Hugo Bilouro and worked

Whats the current status of this task?
Is there a chance to get this into the 2.2.0 release?

+1

+All our company

Is anything going to be done about this anytime soon? This "feature" is really blocking useful reporting within my organisation. A fix so that the time estimated and time spent is calculated consistently would be greatly appreciated. I would also far prefer to update my Redmine instance from an official source rather than hack/configure my existing production system.

+1

Based on Sylvain's suggestion, I've managed to fix this issue on our v2.1.4 installation.

• app/models/query.rb line 406:
  

  @available_columns.insert index, QueryColumn.new(:total_spent_hours,
    :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id OR #{TimeEntry.table_name}.issue_id IN (SELECT child_issue.id FROM #{Issue.table_name} child_issue WHERE child_issue.parent_id = #{Issue.table_name}.id))",
    :default_order => 'desc',
    :caption => :label_spent_time
  )
  

• app/helpers/queries_helper.rb line 59 (format the decimal value):
  

  elsif  column.name == :spent_hours || column.name == :total_spent_hours
    sprintf "%.2f", value
  

The total spent time per issue (including time spent on child issues) is nicely show in the issues list, sorting (taking into account only time spent on first level children) and exporting also work.

There is a smarter way to apply this fix rather than directly modifying Redmine scripts, but this is beyond me since I have almost zero experience with Ruby and RoR.

Ovidiu Stanciu wrote:

• app/models/query.rb line 406:
  [...]

Hi,

this should be also achieved with this:

SELECT COALESCE(SUM(hours), 0) 
FROM #{TimeEntry.table_name} time_entry
LEFT JOIN #{Issue.table_name} child_issue  
        ON time_entry.issue_id = child_issue.id
WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id 
OR child_issue.parent_id IS NOT NULL

IN could be very slow in some cases with many subissues.

I have edited the code on our 2.1.2 system as a workaround for this problem. Many thanks to Victor, Sylvain, Ovidiu and Daniel (authors of notes 3, 5, 14 and 15 respectively) for providing the information to do this.

I am surprised to see that this issue (which is a definite hindrance to accurate reporting) has not yet become a release candidate after seven months of being initially reported.

Hi,

What else needs to be done with this feature to make it go into a scheduled release? It would be good to have this incorportated into a production version of Redmine ASAP.

Best wishes,

Ben

Please take into account that both fixes in query.rb (mine and Daniel's) only take into account time spent on direct descendants when sorting. In case there are multiple levels, the order won't be consistent with the values displayed.

Example :

If displaying only top level issues and sorting ascending by total time spent:

15 comes before 10 because the query fails to take into account the 10 hours spent on issue 5, which is a third level child.

I imagine this is pretty hard to achieve in a single query and I think there should be some recursion involved, as when calculating :total_spent_hours on an issue.

This should be addressed in order to achieve a complete fix.

Best regards,
Ovidiu Stanciu

+1
running 2.3.0, was really hoping for this fix.

+1
I suppose that it will be the best solution to have two issue attributes for "Spent Time" that should be visible at Issues List and Issue details.
One of those two attributes might be "Spent Time" that should display time spent on single issue (without Time logs from child issues).
Another attribute might be "Cumulative Spent Time" that should contain all time logs from selected issue and all childs/grandchilds/grand...grandchilds.

I've just implemented Henrik's patch on 2.3.1 and it works. It also meets Ovidiu's criteria in that a change of spent time in the sub-task of a sub-task is reflected in the parent.

I'm using Redmine 2.3.1. The fix from Bed Dalling (note-24) seams to work for the Web User Interface (HTML). Thank youGreat work!

• Currently the estimated_hours of the parent issue is the sum of the child issues (in REST API as well as in Web Interface). --> good
• But the spent_hours are accumulated in the Web Interface but not in the REST API --> could be improved

Fixing that minor inconsistency for estimated_hours and spent_hours would simplify matters, when generating project reports using the REST API (JSON) - if the project makes use of parent issues to model a Work Breakdown Structure.

In addition "VD DV"s proposal in note-23 could further simpliy matters.

See #5303#note-10 for another issue regarding REST API and spent_hours.

Best Regards,
David

I'm using Redmine 2.3.3 and tried Henrik's patch. But it didn't work and the spend time column was disappeared.
Please help me....

UPDATED: It's working
I forgot add spent time in option view of issues list.
Be lucky.

But when I sorted a spent time column, It displayed Error 500.
How can I fix it??
Thanks.

To solve such a bug, we need more info. Error 500 is nothing which helps us. Please provide corresponding log entries.

This issue is also actual for spent time reports.

+1 please fix this inconsistency

+1

+1

+1

We've just installed Redmine 3.0.3 and we're having the same problem - is there a patch for this version?

Thanks for this great feature, Jean-Philippe!

it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.
Let me add: Having applied this part by myself, I was able to see "total spent time" column in the query list. This is a great feature in deed.

Ryosuke Hirai wrote:

it seems that issue_query.rb @ Revistion 14406 was not applied to the source code of 3.1.0. I could not find this part after downloading.

Good catch ;) Redmine 3.1.0 is indeed missing this feature due to the fact that the related commits weren't merged in source:/branches/3.1-stable when 3.1.0 got tagged. They actually still aren't. I've reported these issues in #20456.

Thanks for reporting this omission. Mischa.

I have merged trunk r14406 to r14410 to 3.1-stable.