Query to show resolved by value (MSSQL & PGSQL)

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?