Added in v.6.0.100
The report below lists the average time that it takes for a ticket to be set to the ‘Closed’ date for each month.
The report will only list users that meet all of the following criteria:
- The ticket has been set to the ‘Closed’ state.
- The notes are not sent in a ticket that has been set to ‘Ignore’.
Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Avg(Cast(DateDiff(ss, htblticket.date, ClosedDate.CloseDate) As decimal) /
86400) As AverageDays
From htblticket
Inner Join (Select Top 1000000 htblticket.ticketid,
Max(htblhistory.date) As CloseDate
From htblticket
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblhistory On htblhistory.ticketid = htblticket.ticketid
Inner Join htblticketstates htblticketstates1 On htblhistory.ticketstateid =
htblticketstates1.ticketstateid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblticketstates.statename = 'Closed' And htblticketstates1.statename =
'Closed' And htblhistorytypes.name In ('status changed',
'Note added and state changed', 'Internal note added and state changed')
Group By htblticket.ticketid) As ClosedDate On ClosedDate.ticketid =
htblticket.ticketid
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)
Order By Year Desc,
Month Desc
