Helpdesk: Time worked per agent, last 7 days (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below lists the sum of the time worked values per agent that was added in the last 7 days.

The report will only list users that meet all of the following criteria:

• The time worked is added to the note of an agent.
  
• The time worked is added to a note that was sent in the last 7 days.
  
• The time worked is not added to notes in a ticket that has been set to ‘Ignore’.
  

Select Top 1000000 htblusers.name As Agent,
  htblusers.username,
  htblusers.userdomain,
  Case htblagents.active When 1 Then 'Yes' Else 'No' End As IsLicenced,
  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 htblusers
  Inner Join htblagents On htblusers.userid = htblagents.userid
  Left Join (Select Top 1000000 htblnotes.userid As UserID,
    Sum(htblnotes.timeworked) As MinutesWorked
  From htblnotes
    Inner Join htblticket On htblticket.ticketid = htblnotes.ticketid
  Where htblnotes.date > GetDate() - 7 And htblnotes.timeworked Is Not Null And
    htblticket.spam <> 'True'
  Group By htblnotes.userid) As WorkTime On htblagents.userid = WorkTime.UserID
Where htblusers.name <> 'system'
Order By WorkTime.MinutesWorked Desc,
  Agent