‎08-09-2017 01:24 PM
Select Top 1000000 htblusers.name As Agent,
htblusers.username,
htblusers.userdomain,
Case htblagents.active When 1 Then 'Yes' Else 'No' End As IsLicenced,
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
From htblusers
Inner Join htblagents On htblusers.userid = htblagents.userid
Left Join (Select Top 1000000 htblnotes.userid As UserID,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Inner Join htblticket On htblticket.ticketid = htblnotes.ticketid
Where htblnotes.date > GetDate() - 7 And htblnotes.timeworked Is Not Null And
htblticket.spam <> 'True'
Group By htblnotes.userid) As WorkTime On htblagents.userid = WorkTime.UserID
Where htblusers.name <> 'system'
Order By WorkTime.MinutesWorked Desc,
Agent
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now