Task related reports (MSSQL)
Tested in MSSQL build (14306)
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 "Description",
tc.COMMENT As "Task comments",
taskgroup.QUEUENAME AS "Group",
taskowner.FIRST_NAME AS "Owner",
taskcreatedby.FIRST_NAME AS "Requester",
LONGTODATE(taskdet.CREATEDDATE) AS "Created Date",
taskstatus.STATUSNAME AS "Task Status",
taskprob.PROBLEMID AS "Problem ID",
taskchange.CHANGEID AS "Change ID",
taskproject.PROJECTID AS "Project Id",
wotask.WORKORDERID AS "RequestID",
LONGTODATE(TasDetHistory.OPERATIONTIME) As "Task Updated time"
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 QueueDefinition taskgroup ON taskdet.GROUPID=taskgroup.QUEUEID
LEFT JOIN WorkOrderToTaskDetails wototaskdet ON taskdet.TASKID=wototaskdet.TASKID
LEFT Join TaskToComment ttc ON taskdet.TASKID=ttc.TASKID
LEFT JOIN Comments TC ON ttc.TASKID=tc.COMMENTID
LEFT JOIN TaskDescription taskdesc ON taskdet.TASKID=taskdesc.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 ON "taskdet"."TASKID"="TaskToProjects"."TASKID"
LEFT JOIN ProjectAccMapping ON "TaskToProjects"."PROJECTID"="ProjectAccMapping"."PROJECTID"
LEFT JOIN AccountSiteMapping ON taskdet.SITEID=AccountSiteMapping.SITEID
LEFT JOIN (select max(tdhistory.OPERATIONTIME) AS OPERATIONTIME,tdhistory.TASKID AS TaskID from TaskDetailsHistory tdhistory group by tdhistory.TASKID) TasDetHistory ON taskdet.TASKID=TasDetHistory.TASKID
==============
Tested in SQL / 10524 build
==============
New to ADSelfService Plus?
Related Articles
Query to show Site and its related settings(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute the query. select adef.org_name "Account Name",sdef.siteid "Site ID", sdo.name "Site Name", siteconf.settingdetail "Related Settings", CASE WHEN ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to show Request and associated task details (PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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", ...Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...Query to show last comments added in Projects task_MSSQL
SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...