Query to show on hold status reason/comments

Query to show on hold status reason/comments

‚ÄčPGSQL & MSSQL:

SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS "Priority", wo.TITLE AS "Subject",wodm.Dependsonid "Depends on", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester",  LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Upd Time",std.STATUSNAME AS "Request Status",ohs.onholdcomments "Comments",wos.ISOVERDUE AS "Overdue Status" FROM WorkOrder wo

 

LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID

 

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID

 

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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID

 

LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID

 

LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID

 

LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID

 

LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID

 

LEFT JOIN WODependency wod ON wo.workorderid=wod.workorderid

 

LEFT JOIN WODependencyMarking wodm ON wod.Reqdependencyid=wodm.Reqdependencyid

 

LEFT JOIN ONHOLDSCHEDULER ohs on ohs.workorderid=wo.workorderid

 

WHERE (wo.ISPARENT='1') 
          • Related Articles

          • Query to show both task comments and worklog comments

            MSSQL: SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category",  "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS "Owner", "taskdet"."TITLE" ...
          • 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", ...
          • Query to show last comments added in Projects task_MSSQL

            SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...
          • 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", ...