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 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 - ...
          • Query to show Field and Form rules of each template

            MSSQL: SELECT rtf.templatename "Template Name",  STUFF((SELECT ', '+ ad.org_name + char(10)   FROM  Accountdefinition ad LEFT JOIN Ruleaccountmapping ramp ON ramp.accountid=ad.org_id where rd.ruleid=ramp.ruleid FOR XML PATH ('')), 1, 1, '') 'Rule ...