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 Status", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Upd Time",taskdet.TITLE AS "Task Title",taskstatus.STATUSNAME AS "Task Status", taskowner.FIRST_NAME AS "Task Owner", taskdet.PER_OF_COMPLETION AS "Percentage Of Completion" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN workordertotaskdetails wtd on wtd.workorderid=wo.workorderid LEFT JOIN taskdetails taskdet on taskdet.taskid=wtd.taskid LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID LEFT JOIN WorkOrder_Fields wof on wof.workorderid=wo.workorderid WHERE wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear> AND (taskstatus.STATUSNAME='Closed' OR taskstatus.STATUSNAME='Resolved') AND (std.STATUSNAME!='Closed' AND std.STATUSNAME!='Close Requested') AND wof.UDF_CHAR13='YES' GROUP BY Wo.workorderid,pd.PRIORITYNAME,cd.CATEGORYNAME,aaU.FIRST_NAME,ti.FIRST_NAME,wotodesc.FULLDESCRIPTION,std.STATUSNAME,wos.LAST_TECH_UPDATE,taskdet.TITLE,taskstatus.STATUSNAME,taskowner.FIRST_NAME,taskdet.PER_OF_COMPLETION ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query to show project tasks status wise count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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 ...Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...