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
Change Risk Assessment Questionnaire using Custom module - Implementation steps
We’re excited to share a step-by-step guide( Attached) on implementing a Change Risk Assessment Questionnaire in ServiceDesk Plus using the Custom Module feature. This solution leverages Custom Modules, Change Additional Fields, and Field & Form ...Task related reports (MSSQL)
Tested in MSSQL build (14306) Sample Output and headers present in this report Goto reports -> New query report and executes the following SELECT taskdet.TASKID AS "Task ID", taskdet.MODULE AS "Module", taskprior.PRIORITYNAME AS "Priority", ...Scheduled Reports
This report is used to get the complete list of all the scheduled reports with the owner name configured in the application. SELECT CustomReport_Details.REPORT_NAME "Report Name", AaaUser.FIRST_NAME "Owner" FROM ReportScheduleTask LEFT JOIN ...Known issue in Reports throwing error in UI
Issue - Reports thrown error in UI Trace in Logs. [12:23:37:156]|[02-13-2023]|[SYSERR]|[INFO]|[27512]: Thread Thread[Thread-129,5,main] exited unexpectedly.java.lang.NoClassDefFoundError: Could not initialize class ...Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent) (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) This report contains the time analysis for each status/group/technician changes in a request, kindly execute this under Reports->New Query Report. Status Changes: SELECT wo.WORKORDERID AS "Request ID", ...