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", ...
          • 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 ...
          • 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 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.employeeid "Employee ID",sdu.jobtitle "Job Title",aci.emailid ...
          • How to add hyperlinks in Advisory for MSP Account ?

            Under Account Tab>Editing the Account will have an advisory field in which we can configure hyperlinks. However, under Admin>Oranization Details the feature is not available.  Feature ID: 13986 Ticket for reference: 5700120  Workaround 1. Click on ...