cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Nicki_Skovlund
Engaged Sweeper
Hi,

Is it possible to create a report that shows all helpdesk tickets that has a secondary agent assigned?
So it shows both the primary agent and the secondary.

Kind regards
Nicki
1 ACCEPTED SOLUTION
brandon_jones
Champion Sweeper III
Try this:

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,
htblusers3.name As SubscribedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject
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 htblticketsubscribers On htblticket.ticketid =
htblticketsubscribers.ticketid
Inner Join htblagents htblagents1 On htblagents1.agentid =
htblticketsubscribers.agentid
Inner Join htblusers htblusers3 On htblusers3.userid = htblagents1.userid
Where htblticket.spam <> 'True'
Order By htblticket.ticketid

View solution in original post

3 REPLIES 3
Nicki_Skovlund
Engaged Sweeper
Thank you very much Brandon

This is exatly what i needed, my sql skill are just not the best.
brandon_jones
Champion Sweeper III
Try this:

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,
htblusers3.name As SubscribedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject
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 htblticketsubscribers On htblticket.ticketid =
htblticketsubscribers.ticketid
Inner Join htblagents htblagents1 On htblagents1.agentid =
htblticketsubscribers.agentid
Inner Join htblusers htblusers3 On htblusers3.userid = htblagents1.userid
Where htblticket.spam <> 'True'
Order By htblticket.ticketid

Is it possible to create a report that shows active tickets (with an assigned agent) that don't have a secondary agent assigned?  By active tickets I mean all tickets that don't have a ticket state of "Closed".  Thank you.