Assessment reports
Datebase: MSSQL
Status wise:
Status wise:
select wo.workorderid, sd.statusname,convert(varchar(5),(sum(wsi.timespent))/60)+':'+convert(varchar(5),((sum(wsi.timespent))%60))'timespent' from workorder wo join wo_assessment wa on wo.workorderid=wa.workorderid join wo_status_info wsi on wa.assessmentid=wsi.assessmentid left join statusdefinition sd on wsi.statusid=sd.statusid where wsi.endtime is not null AND wo.departmentid=1202 AND (dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2016-04-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2016-04-30 23:59',21)) group by sd.statusname,wo.workorderid order by wo.workorderid
Support Rep wise :
select wo.workorderid, case when aau.first_name is null then 'Unassigned' else aau.first_name end as name, convert(varchar(5),(sum(wti.timespent))/60)+':'+convert(varchar(5),((sum(wti.timespent))%60))'timespent' from workorder wo join wo_assessment wa on wo.workorderid=wa.workorderid join wo_tech_info wti on wa.assessmentid=wti.assessmentid left join departmentuser depu on wti.technicianid=depu.departmentuserid left join aaauser aau on wti.technicianid=aau.user_id where wo.createdtime>1442901165180 and wti.endtime is not null group by aau.first_name,wo.workorderid order by wo.workorderid;
Group Wise
select wo.workorderid,case when qd.Queuename is null then 'Unassigned' else qd.Queuename end as 'Group', convert(varchar(5),(sum(wgi.timespent))/60)+':'+convert(varchar(5),((sum(wgi.timespent))%60))'timespent' from workorder wo left join wo_assessment wa on wo.workorderid=wa.workorderid left join wo_group_info wgi on wa.assessmentid=wgi.assessmentid left join queuedefinition qd on qd.Queueid=wgi.Groupid where wo.createdtime>1442901165180 and wgi.endtime is not null group by qd.Queuename,wo.workorderid order by wo.workorderid
Mysql:
Status
select wo.workorderid, sd.statusname, case when (sum(wsi.timespent)%60) = 0 then sum(wsi.timespent) else concat(FLOOR(sum(wsi.timespent)/60), ' hours ', sum(wsi.timespent)%60, ' minutes') end as time_spent from workorder wo join wo_assessment wa on wo.workorderid=wa.workorderid join wo_status_info wsi on wa.assessmentid=wsi.assessmentid left join statusdefinition sd on wsi.statusid=sd.statusid where wsi.endtime is not null and wo.createdtime>1442901165180 group by wo.workorderid, wsi.assessmentid;
Support Rep
select wo.workorderid, case when aau.first_name is null then 'Unassigned' else aau.first_name end as name, case when (sum(wti.timespent)%60) = 0 then sum(wti.timespent) else concat(FLOOR(sum(wti.timespent)/60), ' hours ', sum(wti.timespent)%60, ' minutes') end as time_spent from workorder wo join wo_assessment wa on wo.workorderid=wa.workorderid join wo_tech_info wti on wa.assessmentid=wti.assessmentid left join aaauser aau on wti.technicianid=aau.user_id where wo.createdtime>1442901165180 group by wo.workorderid,wti.assessmentid;
Group wise:
select wo.workorderid, case when qd.Queuename is null then 'Unassigned' else qd.Queuename end as 'Group', case when (sum(wgi.timespent)%60) = 0 then sum(wgi.timespent) else concat(FLOOR(sum(wgi.timespent)/60), ' hours ', sum(wgi.timespent)%60, ' minutes') end as time_spent from workorder wo left join wo_assessment wa on wo.workorderid=wa.workorderid left join wo_group_info wgi on wa.assessmentid=wgi.assessmentid left join queuedefinition qd on qd.Queueid=wgi.Groupid where wo.createdtime>1442901165180 and wgi.endtime is not null group by qd.Queuename,wo.workorderid order by wo.workorderid
PGSQL
Status Wise
select wo.workorderid "Request ID", sd.statusname "Status", concat(FLOOR(sum(wsi.timespent)/60), ' hours ', sum(wsi.timespent)%60, ' minutes')"Time spent" from workorder wo Left join wo_assessment wa on wo.workorderid=wa.workorderid join wo_status_info wsi on wa.assessmentid=wsi.assessmentid left join statusdefinition sd on wsi.statusid=sd.statusid where wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-04-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-05-01 00:00:00') * 1000 AS BIGINT) and wo.completedtime!=0 group by wo.workorderid,sd.statusname order by wo.workorderid
Total Sum : Support Rep wise
Total Sum : Support Rep wise
select wo.workorderid "Request ID",
concat(FLOOR(sum(wti.timespent)/60), ' hours ', sum(wti.timespent)%60, ' minutes')"Time spent" from workorder wo
Left join wo_assessment wa on wo.workorderid=wa.workorderid
Left join wo_tech_info wti on wa.assessmentid=wti.assessmentid
left join aaauser aau on wti.technicianid=aau.user_id where wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-04-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-05-01 00:00:00') * 1000 AS BIGINT) and wo.completedtime!=0 group by wo.workorderid
Support Rep Wise:
select wo.workorderid "Request ID", aau.first_name "Support Rep", concat(FLOOR(sum(wti.timespent)/60), ' hours ', sum(wti.timespent)%60, ' minutes')"Time spent" from workorder wo Left join wo_assessment wa on wo.workorderid=wa.workorderid Left join wo_tech_info wti on wa.assessmentid=wti.assessmentid left join aaauser aau on wti.technicianid=aau.user_id where wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-04-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2016-05-01 00:00:00') * 1000 AS BIGINT) and wo.completedtime!=0 group by wo.workorderid, aau.first_name
Group Name
select wo.workorderid "Request ID",qd.Queuename "Group",concat(FLOOR(sum(wgi.timespent)/60), ' hours ', sum(wgi.timespent)%60, ' minutes') "Time spent" from workorder wo Left join wo_assessment wa on wo.workorderid=wa.workorderid left join wo_group_info wgi on wa.assessmentid=wgi.assessmentid left join queuedefinition qd on qd.Queueid=wgi.Groupid where wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2017-04-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2017-06-01 00:00:00') * 1000 AS BIGINT) and wo.completedtime!=0 and wgi.endtime is not null group by wo.workorderid, qd.Queuename order by wo.workorderid,qd.Queuename
MSSQL
SELECT wo.WORKORDERID "Request ID",max(wo.TITLE) "Subject",
min(sd1.statusname) "From status",
min(sd2.statusname) "To status",
wsi.timespent "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid WHERE (wo.ISPARENT='1') and sd2.statusname = 'Resolved' group by wo.WORKORDERID,wsi.timespent
New to ADSelfService Plus?
Related Articles
Steps for deleting/modifying the reports created by other techs:
Steps for deleting/modifying the reports created by other techs: Connect to your database To connect to your database please refer http://www.manageengine.com/products/support-center/faq-general.html Execute the below query which will give you the ...Changing the ownership of Report
In order to change the ownership of the existing Reports, please follow the below steps, 1. connect to your database. Incase, if your connecting database is postgresql, please refer the below link to connect to it. viz. ...Issue running reports
Cause : Report API running the background causes the issue. If the user mentions issue with running reports with the below error and trace as mentioned below. Error trace: ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...ZOHO reports or Analytics Plus Tables reference / issues troubleshooting
When you face an issue in ZOHO reports or Analytics Plus issue, please send the below details for troubleshooting the same. Save the ZOHO reports / Analytics plus configuration once and gather the below files : 1. zip the log folder present under ...