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  


          • 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 ...
          • 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 to get Requesters details for each account

            PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ...
          • Query to show status changes of a task

            PGSQL & MSSQL: SELECT taskdet.taskid "Task ID", taskdet.TITLE "Task Title", std.STATUSNAME "Task Status", aau.first_name "Task Owner", aau1.first_name "Status changed by", LONGTODATE(tdh.operationtime) "Status changed in (Time)", std1.STATUSNAME ...