Query to show resolved by value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
PGSQL & MSSQL:
SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", aau.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", std.STATUSNAME AS "Request Status", dpt.DEPTNAME AS "Department", sdo.NAME AS "Site", rtdef.NAME AS "Request Type", mdd.MODENAME AS "Mode", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", requesttemplate_list.templatename "Template Name", cri.FIRST_NAME AS "Created By", aau2.first_name "Resolved By", wo.TITLE AS "Title", wo.description "Description", rrs.RESOLUTION AS "Resolution", pd.PRIORITYNAME AS "Priority", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Updated Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID
LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
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 ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID
LEFT JOIN requesttemplate_list on wo.templateid=requesttemplate_list.templateid
LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID
left join workorderhistory woh on wo.workorderid=woh.workorderid LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID left join sduser sdu2 on woh.operationownerid=sdu2.userid left join aaauser aau2 on sdu2.userid=aau2.user_id WHERE (wo.ISPARENT='1') AND woh.workorderid=wo.workorderid AND woh.historyid = (select max(historyid) from workorderhistory woh1 where woh1.workorderid=wo.workorderid and woh1.operation='RESOLVED')
New to ADSelfService Plus?
Related Articles
Query to show Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT rd.REGIONNAME "Region", count(wo.workorderid) "Logged", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog" FROM ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query on current asset value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", ...Query to show tickets that are in resolved status for more than 1 hour ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Request ID",ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID INNER JOIN Accountsitemapping asm ON wo.siteid=asm.siteid INNER JOIN ...Query to show total number of calls received and resolved per month (MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created", count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END) "Closed" FROM WorkOrder wo ...