-- ## 11: Show all closed defect & enhancement tickets ## --

-- based on: 
-- {{{
-- http://groups.google.com/group/trac-users/browse_thread/thread/c3452e8656af5988/62c9ebdee6ec91ea?hl=en&lnk=gst&q=datetime#62c9ebdee6ec91ea
-- }}}

select id, type, summary, component, milestone, owner,

  datetime((select max(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed'),
            'unixepoch', 'localtime') as closedate,
  (select max(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue =
'closed')
   as closetime
  from ticket t
where status = 'closed'
 AND type IN ('defect','enhancement')
 order by -closetime