Query to show Request and associated task details

Query to show Request and associated task details

PGSQL & MSSQL:

SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", qd.QUEUENAME AS "Group", std.STATUSNAME AS "Request Status", longtodate(wo.CREATEDTIME) AS "Created Time", longtodate(wo.DUEBYTIME) AS "DueBy Time", rcode.NAME AS "Request Closure Code", wos.CLOSURECOMMENTS AS "Request Closure Comments", sdo.NAME AS "Site", ad.ORG_NAME AS "Account", tk.taskid "Task ID", tk.title "Task Title", taskowner.FIRST_NAME AS "Owner", tk.PER_OF_COMPLETION "Percentage Of Completion", LONGTODATE(tk.CREATEDDATE) "Created Date",  LONGTODATE(tk.SCHEDULEDSTARTTIME) "Scheduled Start Time",  LONGTODATE(tk.SCHEDULEDENDTIME) "Scheduled End Time", taskprior.PRIORITYNAME "Task Priority", taskstatus.STATUSNAME "Task Status", tasktype.TASKTYPENAME "Task Type" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 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 workordertotaskdetails wotc ON wotc.workorderid=wo.workorderid LEFT JOIN taskdetails tk on tk.taskid=wotc.taskid LEFT JOIN taskdescription tkd on tkd.taskid=tk.taskid LEFT JOIN Aaauser aau2 ON tk.createdby=aau2.user_id LEFT JOIN Sduser sdu2 ON aau2.user_id=sdu2.userid LEFT JOIN SDUser taskownersdu ON tk.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN PriorityDefinition taskprior ON tk.PRIORITYID=taskprior.PRIORITYID LEFT JOIN StatusDefinition taskstatus ON tk.STATUSID=taskstatus.STATUSID LEFT JOIN TaskTypeDefinition tasktype ON tk.TASKTYPEID=tasktype.TASKTYPEID
          • Related Articles

          • Query to view task details with account

            For Postgres and MS SQL SELECT org_name "Account", "taskmilestone"."PROJECTID" AS "Project id", "taskdet"."TASKID" AS "Task ID", "taskdet"."TITLE" AS "Title", "taskcreatedby"."FIRST_NAME" AS "Created By", "taskowner"."FIRST_NAME" AS "Owner", ...
          • Query to show Problems, its associated incidents and change_ MSSQL

            SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency",  "statdef"."STATUSNAME" AS "Problem Status", "impactdef"."NAME" AS "Problem Impact", ...
          • Query to show request details along with technician's department

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...
          • Query to show both task comments and worklog comments

            MSSQL: SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category",  "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS "Owner", "taskdet"."TITLE" ...
          • Query to show request task details

            PGSQL: SELECT wo.WORKORDERID AS "Request ID",  tk.taskid "Task ID", tk.title "Task Title", tk.module "Task Module", sdu.firstname "Task Created By", LONGTODATE(tk.createddate) AS "Task Created Date", wo.TITLE AS "Subject", tk.isparent "Is Parent", ...