CODEWITHSUNDEEP
sqlitereporttrac
a coder
a coder

Reputation: 7639

Include date closed in Trac custom report

I'm using the following to show tickets that have been closed, with newly closed tickets at the top:

SELECT 
   p.value AS __color__,
   id AS ticket, 
   summary, 
   component,
   version,
   milestone,
   t.type AS type, 
   owner, 
   status,
   time AS created,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status = 'closed'
  ORDER BY changetime DESC, time DESC, CAST(p.value AS integer), milestone, t.type, time

Here are the columns that currently show:

Ticket
Summary
Component
Version
Milestone
Type
Owner
Status
Created

I'd like to add the date closed to the report view (and perhaps a third column showing the date difference). How would I go about this?

Upvotes: 3

Views: 573

Answers (2)

hasienda
hasienda

Reputation: 2390

Adding the closed date is not as straight-forward as one me think. The following example will tell you the trick:

SELECT p.value AS __color__,
       ticket, summary, component, version, milestone, t.type, owner,
       t.time AS created,
       MAX(tc.time) as date
  FROM ticket_change tc
  LEFT JOIN ticket t ON tc.ticket=id
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
 WHERE field='status'
   AND newvalue='closed'
 GROUP by ticket
 ORDER BY tc.time DESC, t.time DESC, CAST(p.value AS integer), milestone, t.type

You need the JOIN on the 'ticket_change' db table and last-first sorting to reliably get the last close date.

Upvotes: 1

lc.
lc.

Reputation: 116438

To show a field on the report view, as opposed to just the RSS feed, remove the leading underscore from the column name. changetime AS _changetime is the offending line; try changing it to changetime AS closed. Note: you may have to fix it up like datetime(changetime/1000000, 'unixepoch') AS closed.

To get the date difference, try subtracting the two columns, each wrapped in a call to the SQLite julianday function, like julianday('now') - julianday(changetime/1000000, 'unixepoch') AS closedago.

Upvotes: 2

Related Questions