Querying the Redmine database to find when an issue was closed is not
straightforward. Firstly, this is not readily available in the journal_details
table,
but the matter is further complicated by the fact that once closed, an issue
may be re-opened.
Needing to find the date a ticket was closed is often important for producing reports for management. It’s also the sort of shit that ISO9001 auditors like.
So, here’s my SQL to find the date that a ticket was closed. It puts the results into a temporary table, ready for further querying.
CREATE TEMPORARY TABLE isoTicketClosed ( id int NOT NULL PRIMARY KEY, datOpened datetime NOT NULL, datClosed datetime NOT NULL ) INSERT INTO isoTicketClosed (id, datOpened, datClosed) SELECT i.id, i.created_on, MAX(j.created_on) FROM issues i INNER JOIN journals j ON i.id = j.journalized_id INNER JOIN journal_details jd ON j.id = jd.journal_id INNER JOIN issue_statuses js ON jd.value = js.id WHERE j.journalized_type = 'Issue' AND jd.prop_key = 'status_id' AND js.is_closed = 1 AND NOT EXISTS ( -- a later re-opening SELECT NULL FROM journals zj INNER JOIN journal_details zjd ON zj.id = zjd.journal_id INNER JOIN issue_statuses zjs ON zjd.value = zjs.id WHERE zj.journalized_id = j.journalized_id AND zj.created_on > j.created_on AND zjd.prop_key = 'status_id' AND zjs.is_closed = 0 ) GROUP BY i.id, i.created_on