[rt-users] Ticket Status History

Ruslan Zakirov ruz at bestpractical.com
Thu Mar 31 11:36:25 EDT 2011


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'))

  WHERE
    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 <estoycon at gmail.com> wrote:
> Hello everyone.
> 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?
>
> Thanks
> Eric
>
>



-- 
Best regards, Ruslan.



More information about the rt-users mailing list