Below script gives an overview of all the tickets by Date / Priority / user / agent / Assigned team / Category / Subject.
There is still a problem that a ticket is shown twice when it was assigned from 1st to 2nd line team, is there a way to only show 2nd line team if there are 2 records for a ticketid?
Select Distinct Top 1000000 htblticket.ticketid As Id,
htblticket.date As [Date Opened],
htblticketstates.statename As [Ticket State],
htblpriorities.name As Priority,
htblusers.name As [User],
htblusers1.name As Agent,
htblteams.teamname,
htblticketcustomfield.data As Categorie,
'http://www.intranet.dendermonde.be:81/helpdesk/Ticket.aspx?tid=' +
Cast(htblticket.ticketid As varchar) As hyperlink_Subject,
htblticket.subject As hyperlink_name_Subject,
htblteams.teamid
From htblticket
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Left Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Left Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblagents.userid = htblusers1.userid
Left Join tblAssets On htblticket.assetid = tblAssets.AssetID
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblagentteam On htblagents.agentid = htblagentteam.agentid
Inner Join htblteams On htblteams.teamid = htblagentteam.teamid
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
