Helpdesk: Average time until ticket set to Closed, by month (Built-in)

[Nick_VDB]

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