
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-13-2021 01:47 AM
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?
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
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
Labels:
- Labels:
-
Report Center
1 REPLY 1

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-16-2021 03:03 PM
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
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
