[Extract] Extract data from specific date to an other date.
Added by c4software Valentin over 13 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 Ivan Cenov over 13 years ago
You may want to see this plugin
http://www.redmine.org/plugins/redmine_extra_query_operators
RE: [Extract] Extract data from specific date to an other date.
-
Added by c4software Valentin over 13 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 13 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 13 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 Etienne Massip over 13 years ago
Use Excel to filter the CSV :p
RE: [Extract] Extract data from specific date to an other date.
-
Added by c4software Valentin over 13 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 13 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 13 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 13 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.