SQL custom query
I would like to get help with a custom query which I can get data from Redmine Database with below field and condition
Issue ID/ Tracker/ Subject / Author/ Assignee / Created / Start time / Closed / Status /Custom field 1/ Custom field 2 / Category / Priority
and the condition to get data from date to date ( between ddmmyyyy - ddmmyyyy)
thank you so much !
#3 Updated by Jeremy Bailey almost 4 years ago
Hope this helps you, this is tested for me on PostgreSQL but may not work so well on MySQL. I've had to write similar queries in the past.
I'm assuming that the custom fields are for issues, not projects or something. You'll need to look up the custom field values from the Redmine admin menu for each of your two custom fields, and replace
80 with these ids. The dates can be changed in the query.
You'll most likely want to use ISO date format.
SELECT issues.id ,trackers.name as "tracker" ,issues.subject ,authors.login as "author" ,assignees.login as "assignee" ,issues.created_on ,issues.start_date ,issues.closed_on ,issue_statuses.name as "status" ,custom1.value as "custom_1" ,custom2.value as "custom_2" ,issue_categories.name as "category" ,enumerations.name as "priority" FROM issues LEFT JOIN trackers ON trackers.id = issues.tracker_id LEFT JOIN users authors ON authors.id = issues.author_id LEFT JOIN users assignees ON assignees.id = issues.assigned_to_id LEFT JOIN enumerations ON enumerations.id = issues.priority_id AND enumerations.type = 'IssuePriority' LEFT JOIN issue_statuses ON issue_statuses.id = issues.status_id LEFT JOIN custom_values custom1 ON custom1.customized_id = issues.id AND custom1.customized_type = 'Issue' LEFT JOIN custom_values custom2 ON custom2.customized_id = issues.id AND custom2.customized_type = 'Issue' LEFT JOIN issue_categories ON issue_categories.id = issues.category_id WHERE issues.id IS NOT NULL AND issues.start_date BETWEEN '2016-04-01' AND '2018-01-03' AND custom1.custom_field_id = '2' AND custom2.custom_field_id = '80' GROUP BY issues.id, trackers.name, authors.login, assignees.login, custom1.value, custom2.value, issue_statuses.name, enumerations.name, issue_categories.name