Query report to show Open requests without open tasks

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
      • Related Articles

      • Query to show tasks created per template along with status

        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 used, the subject are not altered.  SELECT ...
      • Query to show time spent added under home tasks.

        Please go to Reports-New Query Report and execute this query. SELECT tk.TASKID "Task ID",MAX(tk.TITLE) "Task Title", cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent ...
      • 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 parent and child requests (Linked Request) and its details. PGSQL & MSSQL

        Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", stdparent.statusname "Parent Request Status", ...
      • Query to show the number of days, the tickets are open_PGSQL

        select wo.WORKORDERID"Request ID", qd.QUEUENAME "Group", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", sdo.NAME "Site", LONGTODATE(wo.CREATEDTIME) "Created Time", round(extract(epoch from(now()::TIMESTAMP - ...