→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎04-13-2021 01:47 AM
Select Top 1000000 htblticket.ticketid,
htblticket.ticketid As Ticket,
htblticket.date As 'Creation Date',
Max(htblhistory.date) As 'Close Date',
htblticketstates.statename As state,
htbltickettypes.typename As Type,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes' As TimeWorked,
OfficeLocation.data As OfficeLocation
From htblticket
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'OfficeLocation') As OfficeLocation On
OfficeLocation.ticketid = htblticket.ticketid
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 htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Inner Join (Select Top 1000000 htblnotes.ticketid,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
htblticket.ticketid
Where htblticketstates.statename = 'Closed' And htbltickettypes.typename =
'EMERGENCY' And OfficeLocation.data LIKE '%Asia'
Group By htblticket.ticketid,
htblticket.date,
htblticketstates.statename,
htbltickettypes.typename,
htblsource.name,
htblusers.name,
htblusers1.name,
'../helpdesk/icons/' + htbltickettypes.icon,
htblticket.subject,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes',
OfficeLocation.data,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.updated,
htblusers2.name,
WorkTime.MinutesWorked
Having Max(htblhistory.date) > GetDate() - 121
Order By WorkTime.MinutesWorked Desc,
htblticket.ticketid
‎04-16-2021 03:03 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now