Time elapsed by each group in requests. PGSQL
Tested in Build PGSQL (14300)
Go to Reports-New Query Report and execute the below query.
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", wotodesc.FULLDESCRIPTION AS "Description", ad.ORG_NAME AS "Account", serdef.NAME AS "Service Category",reqtemp.templatename "Template Name",qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", (wog.TIMESPENT/60)||':'||((wog.TIMESPENT))%60 "Time Elapsed" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID
WHERE (wo.ISPARENT='1')
This works only in PGSQL
With few more columns:
SELECT wo.WORKORDERID AS "Request ID", rtdef.NAME AS "Request Type", ad.ORG_NAME AS "Account", wof.UDF_CHAR1 AS "Client", wof.UDF_CHAR2 AS "Country", pd.PRIORITYNAME AS "Priority", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", std.STATUSNAME AS "Request Status", wos.REOPENED AS "ReOpened", LONGTODATE(wo.CREATEDTIME) "Created Time", qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", (wog.TIMESPENT/60)||':'||((wog.TIMESPENT))%60 "Time Elapsed", LONGTODATE(wo.DUEBYTIME) "DueBy Time", LONGTODATE(wo.RESOLVEDTIME) "Resolved Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", wos.ISOVERDUE "Overdue Status" FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID WHERE wo.createdtime>=<from_lastmonth> AND wo.createdtime<=<to_lastmonth> AND Wo.ISPARENT='1' ORDER BY 1,woa.executedtime
New to ADSelfService Plus?
Related Articles
Time elapsed analysis
This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...Keep track of your requests with Time Elapsed Analysis in ServiceDesk Plus.
Time-elapsed analysis provides information on how long any request was handled by a technician and remained in a group or status. Select any request from the Request List View and click the clock icon next to the History tab in the Request ...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", ...Query to find Time elapsed by each status in requests( MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) MSSQL SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME ...Query to show time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...