[Extract] Extract data from specific date to an other date.

Added by c4software Valentin over 9 years ago

Hi,

I need to extract the number of tickets opened and assigned in January 2011 for/to a specific user in my personnal redmine, someone know the way to do?

I Hope some can help me.

Thanks,

Replies (9)

RE: [Extract] Extract data from specific date to an other date. - Added by c4software Valentin over 9 years ago

Thanks for the quick answer but i use redmine 1.0.1 and the plugin seems not compatible (and i cannot upgrade my redmine for now).

But this is exactly what i want to do.

RE: [Extract] Extract data from specific date to an other date. - Added by Etienne Massip over 9 years ago

Query issues list with filters Assigned to, Author and Created less than 60 days ago and, finally, filter out the issues created on february and december from the CSV export :/

RE: [Extract] Extract data from specific date to an other date. - Added by c4software Valentin over 9 years ago

Yes i have try this too but, with filter like this i don't have tickets assigned to the users and after assigned to an other. For example :

Ticket #2345 Created By User1
Ticket #2345 Assigned to User2
Ticket #2345 Assigned to User1

In my reporting :

User1 : 1 Tickets processed.
User2 : 1 Tickets processed.

But if i extract data with the Query Issue the User2 don't appear. :S

RE: [Extract] Extract data from specific date to an other date. - Added by c4software Valentin over 9 years ago

In the CSV i don't have the information if an tickets is only passed to an user ;).

RE: [Extract] Extract data from specific date to an other date. - Added by Etienne Massip over 9 years ago

You'd need to query the db directly, with some SQL query like :

SELECT
  users.login, users.firstname, users.lastname,
  COUNT(DISTINCT issues_and_assignees.issue_id) AS "#issues" 
FROM
  (SELECT issues.id AS issue_id, issues.created_on AS issue_created_on,
  CAST(journal_details.old_value AS INTEGER) AS "assigned_to_id" 
  FROM issues
    INNER JOIN journals ON journals.journalized_id=issues.id AND journals .journalized_type = 'Issue'
    INNER JOIN journal_details
      ON journal_details.journal_id = journals.id
      AND journal_details.property = 'attr'
      AND journal_details.prop_key = 'assigned_to_id'
  UNION ALL
  SELECT issues.id AS issue_id, issues.created_on AS issue_created_on,
  issues.assigned_to_id
  FROM issues WHERE issues.assigned_to_id IS NOT NULL) AS issues_and_assignees
  INNER JOIN users ON users.id = issues_and_assignees.assigned_to_id
WHERE DATE(issue_created_on) BETWEEN '2011-01-01' AND '2011-01-31'
GROUP BY users.id, users.login, users.firstname, users.lastname

RE: [Extract] Extract data from specific date to an other date. - Added by c4software Valentin over 9 years ago

Hi,

Thanks its seems perfect :).

Thanks a lot for your time.

PS: Just for your information yesterday i have made a request to but this one :

SELECT count(distinct(journalized_id)), left(j.created_on,10), t.name FROM journals j, issues i, users u, journal_details jd, trackers t WHERE j.created_on like "2011-01-%" AND i.id=j.journalized_id AND project_id=1
AND u.id = j.user_id
AND t.id = i.tracker_id
AND jd.journal_id = j.id
AND (( prop_key = 'assigned_to_id'
AND jd.value = '27'))
group by left(j.created_on,10), t.name;

But your request seems better. Thanks a lot.

RE: [Extract] Extract data from specific date to an other date. - Added by Etienne Massip over 9 years ago

Note of these 2 take account of Author field.

You won't get stats for User3 who created issues but has never been assigned to any.

(1-9/9)