[rt-users] Reports based on duration between open and update/closed

[Jesper Henriksen]

On Wed, Mar 24, 2010 at 02:43:36PM +0100, Jesper Henriksen wrote:
> Is there a way to get reports on how much time a ticket has been open?
> For example we would like to see the average time it takes from when a
> customer reports a problem till we have closed the ticket. Bear in mind
> that if the ticket is closed and then re-opened, the duration in which
> it was closed should not be counted.

In case someone else needs an answer to this question, here's some SQL I
came up with after lots of persistent poking around in the database :)
It's probably not perfect, but it's a good start!

If someone can explain how I can show these status timers on RT's search
result page, and perhaps even in graphs, I would be very grateful.

select
    TicketId,
    Subject,
    Status,
    sum(TimeInThisState) as TotalTimeInThisState
from
(
    select
        tr1.ObjectId as TicketId,
        ticket.Subject,
        IF(tr1.Type='Create','new',tr1.NewValue) as Status,
-- Two ways of having the timer show. Uncomment the one you want:
-- Option 1: NULL means "still in this state"
--      unix_timestamp(tr2.Created)-unix_timestamp(tr1.Created) as TimeInThisState
-- Option 2: The clock keeps counting
        unix_timestamp(IF(tr2.Created IS NULL,NOW(),tr2.Created))-unix_timestamp(tr1.Created) as TimeInThisState
    from
        Tickets as ticket,
        Transactions as tr1
    left join Transactions as tr2 on
            tr1.ObjectId=tr2.ObjectId
            and tr1.ObjectType=tr2.ObjectType
            and tr2.id>tr1.id
            and case tr1.Type
                when 'Create' then 'new'
                else tr1.NewValue
            end=tr2.OldValue
    where
        tr1.ObjectType='RT::Ticket'
        and tr1.ObjectId=ticket.EffectiveId
        and tr1.Type in ('Create','Status')
    group by
        tr1.id
    order by
        tr1.id
) as TicketStatusOverview
group by
    TicketId,Status
order by
    TicketId,Status

-- 
Jesper Henriksen