Helpdesk: Count of notes per agent per day, last 30 days (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below lists the amount of notes sent per agent in the last 30 days.

The report will only count notes from tickets that meet all of the following criteria:

• The user is an agent
  
• The ticket has not been set to ‘Ignore’
  

Select Top 1000000 htblusers.name As Agent,
  htblusers.username,
  htblusers.userdomain,
  Case htblagents.active When 'True' Then 'Yes' Else 'No' End As IsLicensed,
  DatePart(dd, htblnotes.date) As NoteDay,
  DatePart(mm, htblnotes.date) As NoteMonth,
  DatePart(yyyy, htblnotes.date) As NoteYear,
  Count(Case When htblnotes.notetype = 3 Then 1 Else Null
  End) As TicketsCreated,
  Count(Case When htblnotes.notetype = 2 Then 1 Else Null End) As InternalNotes,
  Count(Case When htblnotes.notetype = 1 Then 1 Else Null End) As PublicReplies
From htblnotes
  Inner Join htblusers On htblusers.userid = htblnotes.userid
  Inner Join htblagents On htblagents.userid = htblusers.userid
  Inner Join htblticket On htblticket.ticketid = htblnotes.ticketid
Where htblnotes.date > GetDate() - 30 And htblticket.spam <> 'True'
Group By htblusers.name,
  htblusers.username,
  htblusers.userdomain,
  Case htblagents.active When 'True' Then 'Yes' Else 'No' End,
  DatePart(dd, htblnotes.date),
  DatePart(mm, htblnotes.date),
  DatePart(yyyy, htblnotes.date)
Order By Agent,
  NoteYear Desc,
  NoteMonth Desc,
  NoteDay Desc