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. 
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
)
ORDER BY id;
(We are willing to overlook one reopening, since that is more often
than not the requestor writing back to say thank-you
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.
Binand