Query to show when Onhold was placed for a request

Query to show when Onhold was placed for a request

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
          • Related Articles

          • Query to show total time taken to resolve and total time onhold _ MSSQL

            MSSQL: 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", "urgdef"."NAME" AS "Urgency", ...
          • Query to show Total Onhold time_PGSQL

            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", cd.CATEGORYNAME AS "Category", ...
          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • Query to show tickets created based on shift time_PGSQL

            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 Time", extract(hour from ...
          • Query to show ticket first assign/pick up time _ PGSQL

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",ad.org_name "Account", ...