cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
NextCodde
Engaged Sweeper

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!

1 REPLY 1
ErikT
Lansweeper Tech Support
Lansweeper Tech Support

@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