Finding reopened tickets

Hi All,

The boss wants to know which tickets were “reopened”, by unsatisfied
requestors writing back after it was marked as resolved.

For such requests, I usually pick and modify the statusreport.html
to suit my needs. But in this case, I am stumped. :slight_smile:

I came up with this SQL:

SELECT id FROM tickets
WHERE effectiveid IN (
SELECT ticket FROM (
SELECT ticket,count(ticket) AS numreopen
FROM transactions
WHERE field = ‘Status’ AND
oldvalue = ‘resolved’ AND
newvalue = 'open’
GROUP BY ticket
) AS foo
WHERE numreopen >= 2

(We are willing to overlook one reopening, since that is more often
than not the requestor writing back to say thank-you :slight_smile:

How do I go about doing this in DBIx::SearchBuilder? Any pointer will
be greatly appreciated.

RT is 2.0.15, backend is Postgres 7.2.3.