[rt-users] Finding reopened tickets

[Binand Sethumadhavan]

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