Query to show when Onhold was placed for a request (PGSQL)
Tested in Build PGSQL (14300)
PGSQL:
SELECT wo.WORKORDERID AS "Request ID", sdo.NAME AS "Site", cd.CATEGORYNAME AS "Category", longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Current Request Status", longtodate(wo.COMPLETEDTIME) AS "Completed Time", (MAX((wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60)) "Total Time Elapsed in HH:MM", aau1.FIRST_NAME PERFORMEDBY,
LONGTODATE(OPERATIONTIME) "On Hold set time",std1.STATUSNAME "Status Changed From",std2.STATUSNAME "Status Changed To" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
left join StatusDefinition std1 on cast(cast(wohd.prev_value as varchar) as int) =std1.STATUSID
left join StatusDefinition std2 on cast(cast(wohd.current_value as varchar) as int) =std2.STATUSID LEFT JOIN wo_assessment woa ON wo.workorderid=woa.workorderid
INNER JOIN wo_status_info wsi ON woa.assessmentid=wsi.assessmentid
where wohd.COLUMNNAME in ('STATUSID') and std2.STATUSNAME='onhold' and wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-07-31 00:00:00') * 1000 AS BIGINT) GROUP by 1,2,3,5,8,9,10,11 order by 1,OPERATIONTIME desc
New to ADSelfService Plus?
Related Articles
Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...Query to show total time spent of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...Query to show Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...