Query to show Approved tickets per Approver

Query to show Approved tickets per Approver

PGSQL & MSSQL:

SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
std.STATUSNAME "Request Status",
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
ApprovalDetails.EMAIL "Approver Email",
asd.stagename "Stage",
ApprovalStatusDefinition.STATUSNAME "Approval Status",
LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date",
LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
LEFT JOIN approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN approvalstagedefinition asd ON asd.STAGEID=ApprovalDetails.STAGEID
LEFT JOIN aaauser createduser on createduser.user_id=wo.createdbyid
left join workorder_queue ON workorder_queue.workorderid=wo.workorderid 
left join queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
LEFT JOIN ServiceCatalog_Fields scf on scf.workorderid=wo.workorderid
LEFT JOIN requesttemplate_list reqlist on reqlist.templateid=wo.templateid
WHERE ApprovalStatusDefinition.STATUSNAME='Approved' AND ApprovalDetails.EMAIL IN('abc@zoho.com','hari@manageengine.com') AND wo.CREATEDTIME >= DATETOLONG('2020-04-01 07:00:00')  AND wo.CREATEDTIME <= DATETOLONG('2020-04-30 23:59:59')

Note: Approver email ID and Date filter needs to be modified as per the need.
          • Related Articles

          • Query that lists pending approval of a particular service approver:

            PGSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot ...
          • Query to show solution details with approver and associated requests

            MSSQL: SELECT solution.solutionid "Solution ID" , Ad.org_name "Account", max(Solution.TITLE) "Solution Title", max(au.first_name) "Solution Approver", max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords", ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
          • Query_ Changes approved by(approvals tab) and request ID associated to it.

            These are in reference to change initiated due to Requests and who approved the change approval stage alone. MSSQL: SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE("chdt"."CREATEDTIME") AS "Created Time", ...
          • Query to show SLA assigned to tickets

            PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates ...