Something like this:
SELECT t1.OldValue status, AVG(TIMESTAMPDIFF(SECOND, t2.Created, t1.Created))
FROM Transactions AS t1
CROSS JOIN Transactions AS t2
LEFT JOIN Transactions AS t3
ON t3.id < t1.id AND t3.id > t2.id
AND t3.ObjectType = t1.ObjectType AND t3.ObjectId = t1.ObjectType
AND (t3.Type = ‘Status’ OR (t3.Type = ‘Set’ AND t3.Field = ‘Status’))
t1.ObjectType = 'RT::Ticket’
AND (t1.Type = ‘Status’ OR (t1.Type = ‘Set’ AND t1.Field = ‘Status’))
AND t2.ObjectType = t1.ObjectType AND t2.ObjectId = t1.ObjectId
AND (t2.Type = 'Status' OR (t2.Type = 'Set' AND t2.Field =
‘Status’) OR t2.Type = ‘Create’)
AND t2.id < t1.id
AND t3.id IS NULL
GROUP BY t1.OldValue
;On Thu, Mar 31, 2011 at 6:19 PM, Eric Stoycon email@example.com wrote:
I need to create a report which shows the average length of time a ticket is in each status. I tried adapting the ‘include ticket history’ template in a perl script but I cannot seem to get it to include status changes. I’ve dug through the wiki some and the list archive but haven’t come across anything which helps.
Any ideas on how I can pull this info?
Best regards, Ruslan.