Query to view task details with account

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", "taskdet"."PER_OF_COMPLETION" AS "Percentage Of Completion", "taskdet"."ADDTIONAL_COST" AS "Additional Cost", "taskdet"."CREATEDDATE" AS "Created Date", "taskdet"."SCHEDULEDSTARTTIME" AS "Scheduled Start Time", "taskdet"."SCHEDULEDENDTIME" AS "Scheduled End Time", "taskdet"."ACTUALSTARTTIME" AS "Actual Start Time", "taskdet"."ACTUALENDTIME" AS "Actual End Time", "taskdet"."MODULE" AS "Module", "taskprior"."PRIORITYNAME" AS "Priority", "taskstatus"."STATUSNAME" AS "Task Status", "tasktype"."TASKTYPENAME" AS "Task Type", "taskgroup"."QUEUENAME" AS "Group", "wotask"."WORKORDERID" AS "RequestID", "wotask"."TITLE" AS "Request Title", "taskprob"."PROBLEMID" AS "Problem ID", "taskprob"."TITLE" AS "Problem Title", "taskchange"."CHANGEID" AS "Change ID", "taskchange"."TITLE" AS "Change Title", "taskproject"."PROJECTID" AS "Project Id", "taskproject"."TITLE" AS "Project Title", "taskmilestone"."MILESTONEID" AS "Milestone Id", "taskmilestone"."TITLE" AS "Milestone Title", "taskdet"."ISESCALATED" AS "Is Escalated", "taskdet"."ISOVERDUE" AS "Overdue Status", CASE WHEN "taskdet"."ESTIMATEDEFFORT" = 0 THEN 'Not Assigned' ELSE (CASE WHEN "taskdet"."ESTIMATEDEFFORTDAYS" != 0 THEN CAST("taskdet"."ESTIMATEDEFFORTDAYS" AS varchar(10)) + ' Days ' ELSE ''END) + (CASE WHEN "taskdet"."ESTIMATEDEFFORTHOURS" != 0 THEN CAST("taskdet"."ESTIMATEDEFFORTHOURS" AS varchar(10)) + ' Hrs ' ELSE ''END) + (CASE WHEN "taskdet"."ESTIMATEDEFFORTMINUTES" != 0 THEN CAST("taskdet"."ESTIMATEDEFFORTMINUTES" AS varchar(10)) + ' Mins ' ELSE ''END) END AS "Estimated Effort Str" , "taskdet"."ESTIMATEDEFFORT" AS "Estimated Effort" , "taskdesc"."DESCRIPTION" AS "Description"  FROM "TaskDetails" "taskdet" LEFT JOIN "AaaUser" "taskcreatedby" ON "taskdet"."CREATEDBY"="taskcreatedby"."USER_ID" LEFT JOIN "SDUser" "taskownersdu" ON "taskdet"."OWNERID"="taskownersdu"."USERID" LEFT JOIN "AaaUser" "taskowner" ON "taskownersdu"."USERID"="taskowner"."USER_ID" LEFT JOIN "PriorityDefinition" "taskprior" ON "taskdet"."PRIORITYID"="taskprior"."PRIORITYID" LEFT JOIN "StatusDefinition" "taskstatus" ON "taskdet"."STATUSID"="taskstatus"."STATUSID" LEFT JOIN "TaskTypeDefinition" "tasktype" ON "taskdet"."TASKTYPEID"="tasktype"."TASKTYPEID" LEFT JOIN "QueueDefinition" "taskgroup" ON "taskdet"."GROUPID"="taskgroup"."QUEUEID" LEFT JOIN "WorkOrderToTaskDetails" "wototaskdet" ON "taskdet"."TASKID"="wototaskdet"."TASKID" LEFT JOIN "WorkOrder" "wotask" ON "wototaskdet"."WORKORDERID"="wotask"."WORKORDERID" LEFT JOIN "ProblemToTaskDetails" "probtotaskdet" ON "taskdet"."TASKID"="probtotaskdet"."TASKID" LEFT JOIN "Problem" "taskprob" ON "probtotaskdet"."PROBLEMID"="taskprob"."PROBLEMID" LEFT JOIN "ChangeToTaskDetails" "changetotaskdet" ON "taskdet"."TASKID"="changetotaskdet"."TASKID" LEFT JOIN "ChangeDetails" "taskchange" ON "changetotaskdet"."CHANGEID"="taskchange"."CHANGEID" LEFT JOIN "TaskToProjects" "projtotaskdet" ON "taskdet"."TASKID"="projtotaskdet"."TASKID" LEFT JOIN "ProjectDetails" "taskproject" ON "projtotaskdet"."PROJECTID"="taskproject"."PROJECTID" LEFT JOIN "TaskToProjects" "miletotaskdet" ON "taskdet"."TASKID"="miletotaskdet"."TASKID" LEFT JOIN "MileStoneDetails" "taskmilestone" ON "miletotaskdet"."MILESTONEID"="taskmilestone"."MILESTONEID" LEFT JOIN "TaskDescription" "taskdesc" ON "taskdet"."TASKID"="taskdesc"."TASKID" LEFT JOIN "TaskToProjects" ON "taskdet"."TASKID"="TaskToProjects"."TASKID" LEFT JOIN "ProjectAccMapping" ON "TaskToProjects"."PROJECTID"="ProjectAccMapping"."PROJECTID" LEFT JOIN "AccountSiteMapping" ON "taskdet"."SITEID"="AccountSiteMapping"."SITEID" LEFT JOIN taskaccountmapping tam on taskdet.taskid=tam.taskid LEFT JOIN accountdefinition adef on tam.accountid=adef.org_id  




      New to ADSelfService Plus?

      Start your free trial

        Resources

            • Related Articles

            • 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", ...

            • 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 account additional fields _ Details required

              We Need to know whether that is a common account additional fields or add attribute under individual account.   So please send us the following screenshots.   1. Admin-> Account additional fields list view page.   2. Accounts->Edit account and show ...

            • Enable Account Details for basic technicians

              Account Details is an icon that is found right next to the Account drop-down in the application.  For 9.4 Builds By default Account Details tab is shown ONLY for SDAccountAdmin and SDAdmin roles.   1. Take a trimmed backup/MSSQL Backup/Server ...

            • Query for request attachment details

              Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account",  wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  ti.FIRST_NAME AS "Technician",  sa.ATTACHMENTNAME "Attachment Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...