cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Dimitar_Staykov
Engaged Sweeper
Hi I am tryning to build a report with specific parameteres. The most important are Working time and a custom field from htblticketcustomfield, named OfficeLocation. I was able to get the result but can it be optimised some how?


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



Also I would like to have some report with total working time per every OfficeLocation data. For Example:

I have three values for OfficeLocation custom filed: Europe, Asia, USA.
I would like in the report working time for last 120 days to be sumarised per OfficeLocation

For last 120 days
Europe - 06:40:00
USA - 12:04:00
Asia - 8:04:00
1 REPLY 1
Dimitar_Staykov
Engaged Sweeper
I have contacted the Lansweeper support and the built this report code - works great

Select distinct Top 1000000 htblticketcustomfield.data,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))) + ' minutes' As TimeWorked
From htblticketcustomfield
inner join htblcustomfields on htblticketcustomfield.fieldid = htblcustomfields.fieldid
inner join (Select Top 1000000 htblticketcustomfield.data,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
Inner join htblnotes on htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid = htblhistory.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid = htblhistory.ticketstateid
Inner join htblticket on htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
htblhistorytypes.name In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates.statename =
'Closed' and htblcustomfields.name Like '%OfficeLocation%'
group by htblticketcustomfield.data having max(htblhistory.date) > getdate() - 120 ) As Custom1 on Custom1.data = htblticketcustomfield.data