08-30-2022 08:10 AM - last edited on 04-01-2024 01:31 PM by Mercedes_O
Good day,
we use the ticket state "in progress" in a dual use.
When a ticket has a flag and the flagged date is in future we're awaiting something, e.g. a date when the user will return from holiday. A flagged date in the past or today will show us the ticket can be handled again.
Without a flagged date the agent is working on that ticket.
I want to separate those tickets in my report to show that difference but I'm not very confirm in SQL.
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],
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.flagged,
htblticket.flaggeddate
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
Where htblticketstates.statename <> 'Closed' And htblticket.spam <> 'True'
Order By htblticket.ticketid
May I get here some help? Thanks in advance!
09-02-2022 01:53 PM
@NextCodde You can use the sample report below.
It will show all flagged dates in the past or today in a green background color to give a visual indication.
Instructions for adding this report to your Lansweeper installation can be found in this article.
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],
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.flagged,
Case
When htblticket.flaggeddate <= GetDate() Then '#80dec8'
End As backgroundcolor,
htblticket.flaggeddate
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
Where htblticketstates.statename <> 'Closed' And htblticket.spam <> 'True'
Order By htblticket.ticketid
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now