Query reports to know the Ticket wise Approval status

Query reports to know the Ticket wise Approval status


1. Login to SDP MSP as administrator
2. Execute this from SDP MSP application -> Reports -> New Query report


SELECT wo.WORKORDERID AS "Request ID", 
wo.TITLE AS "Subject", 
srep.First_Name As "Suprep",  
qd.QUEUENAME AS "Group",
longtodate(wo.CREATEDTIME) as "Created Time" ,
ApprovalDetails.EMAIL AS "Approver Email",
ApprovalStatusDefinition.STATUSNAME AS "Approval Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
Left Join AAAUser srep ON wos.OwnerID=srep.User_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
 LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
 LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID 
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID 
 left 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
  WHERE ApprovalDetails.ISDELETED!=1 
          • Related Articles

          • Task related reports

            Sample Output and headers present in this report Goto reports -> New query report and executes the following  SELECT  taskdet.TASKID AS "Task ID",  taskdet.MODULE AS "Module",  taskprior.PRIORITYNAME AS "Priority",  taskdesc.DESCRIPTION AS ...
          • Query to show project tasks status wise count

            PGSQL: SELECT projectdet.title AS "Project name", count(case when (taskstatus.statusid='1') THEN 1 ELSE NULL END) "Open Tasks", count(case when (taskstatus.statusid='6') THEN 6 ELSE NULL END) "In Progress Tasks", count(case when ...
          • Query to show ticket aging - PGSQL

            PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name ...
          • Query to know the technician changes in a ticket

            This will show the output only if the technician is assigned/updated in a ticket 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report SELECT wo.WORKORDERID AS "Request ID",  wo.TITLE AS ...
          • Query to find status change, its time and comments.

            Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", ...