Just finished writing a report that I've seen a few requests for. Tickets closed by Agent in the last 7 days. You'll have to find your own AgentID's within the database that correlate to your Agents (I've left ours in for the example).
The report will look like:
Agent Name Tickets Closed
Bob Barker 44
Bruce Wayne 37
Tom Cruise 28
Clark Kent 11
Robert Palmer 4
Ben Stiller 0
If you wish to exclude certain Agent's instead of including them, you can change:
Where htblagents.agentid In ('85', '19', '20', '73', '87', '18', '21', '32')
to
Where htblagents.agentid Not In ('85', '19', '20', '73', '87', '18', '21', '32',
'1', '14', '23')
In this above example, I've excluded the Built-In account and System account as well as 1 additional Agent.
Below, please find the code for the Report.
Select Top 1000000 htblusers.name As [Agent Name],
IsNull(Hist.[Tickets Closed], 0) As [Tickets Closed]
From htblagents
Left Join htblusers htblusers On htblusers.userid = htblagents.userid
Left Join (Select count(*) As [Tickets Closed],
t1.[Agent Name]
From (Select htblhistory.ticketid,
htblhistory.agentid,
htblusers.name As [Agent Name]
From htblhistory
Left Join htblagents On htblagents.agentid = htblhistory.agentid
Left Join htblusers htblusers On htblusers.userid = htblagents.userid
Where htblhistory.ticketstateid = 1 And htblhistory.date > GetDate() - 7
And (htblhistory.typeid = 1 Or htblhistory.typeid = 30 Or
htblhistory.typeid = 50)
Group By htblhistory.ticketid,
htblhistory.agentid,
htblusers.name) t1
Group By t1.[Agent Name]) Hist On Hist.[Agent Name] = htblusers.name
Where htblagents.agentid In ('85', '19', '20', '73', '87', '18', '21', '32')
Order By [Tickets Closed] Desc
Hope someone else can find this useful!
