How to find when a Redmine issue was closed

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

Home | More stuff | Octad of the week