Report with working time, closed state, ticket type against custom filed

[Dimitar_Staykov]

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

[Dimitar_Staykov]

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