Query to show both task comments and worklog comments ( MSSQL )
Tested in MSSQL build (14306)
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" AS "Title", "taskdesc"."DESCRIPTION" AS "Description", "taskstatus"."STATUSNAME" AS "Task Status", "taskdet"."PER_OF_COMPLETION" AS "Percentage Of Completion", c.comment "Task Comments", "ct"."description" AS "Worklog comments", LONGTODATE(ct.createdtime) AS "Last Worklog Added Time" FROM "TaskDetails" "taskdet" LEFT JOIN "SDUser" "taskownersdu" ON "taskdet"."OWNERID"="taskownersdu"."USERID" LEFT JOIN "AaaUser" "taskowner" ON "taskownersdu"."USERID"="taskowner"."USER_ID" 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 "WorkOrder" "wotask" ON "wototaskdet"."WORKORDERID"="wotask"."WORKORDERID" 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 TaskToComment tskc ON tskc.taskid = taskdet.taskid LEFT JOIN Comments c ON tskc.commentid=c.commentid LEFT JOIN WorkOrderToCharge wotoc ON wotask.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN WorkOrderStates wos ON wotask.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN WorkOrder_Fields wof on wof.workorderid=wotask.workorderid ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...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", ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query report to show Problem fields along with last added notes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", ...Request aging with recent worklog comments (MSSQL)
Tested in MSSQL build (14306) SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS "Priority", wo.TITLE AS ...