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
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", ...How Time Elapsed is calculated?
What is Time Elapsed? Time Elapsed is the calculation of the time taken for the completion of the request created with a proposed solution. How Time Elapsed is calculated? ** This calculation depends upon Sites,Operational Hours,Holidays,Request ...Query to show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) ...