Helpdesk: Time worked per ticket (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below gives back the time worked that has been inputted by agents for each ticket.

The report will only take into account tickets that meet all of the following criteria:

• The time worked is not added to notes in a ticket that has been set to ‘Ignore’.

Select Top 1000000 htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date As CreationDate,
  htblticket.updated As LastUpdated,
  htbltickettypes.typename As Type,
  htblticketstates.statename As State,
  htblpriorities.name As Priority,
  htblsource.name As Source,
  htblusers.name As [User],
  htblusers1.name As AssignedAgent,
  htblusers2.name As UserLastNote,
  '../helpdesk/icons/' + htbltickettypes.icon As icon,
  htblticket.subject As Subject,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
  60 / 24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
  60 % 24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
  60))) + ' minutes' As TimeWorked
From htblticket
  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
  Inner Join htbltickettypes On htblticket.tickettypeid =
    htbltickettypes.tickettypeid
  Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
  Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
    htblusers2.userid
  Inner Join (Select Top 1000000 htblnotes.ticketid,
    Sum(htblnotes.timeworked) As MinutesWorked
  From htblnotes
  Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
    htblticket.ticketid
Where htblticket.spam <> 'True'
Order By WorkTime.MinutesWorked Desc,
  htblticket.ticketid