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",
longtodate(projectdet.SCHEDULEDSTARTTIME) "Project Schedule start",
longtodate(projectdet.SCHEDULEDENDTIME) "Project Schedule end",
longtodate(projectdet.ACTUALSTARTTIME) "Project Actual start",
longtodate(projectdet.ACTUALENDTIME) "Project Actual end",
taskdet.PER_OF_COMPLETION "Task Percentage Of Completion",
MAX(c.comment) "Comments" FROM TaskDetails taskdet
LEFT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet.PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
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 StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
LEFT JOIN TaskDescription taskdesc ON taskdet.TASKID=taskdesc.TASKID
LEFT JOIN TaskToComment tskc ON tskc.taskid = taskdet.taskid
LEFT JOIN Comments c ON tskc.commentid=c.commentid
where c.commentid= (select MAX(TaskToComment.commentid) from TaskToComment ) or tskc .taskid is null
GROUP BY TaskDet.TASKID,taskdet.TITLE,taskowner.FIRST_NAME,taskcreatedby.FIRST_NAME,taskstatus.STATUSNAME,taskdesc.DESCRIPTION, c.comment,ProjectDet.SCHEDULEDSTARTTIME,ProjectDet.SCHEDULEDENDTIME,ProjectDet.ACTUALSTARTTIME,ProjectDet.ACTUALENDTIME,TaskDet.PER_OF_COMPLETION
New to ADSelfService Plus?
Related Articles
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 ...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 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", ...Query to show last notes added in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select wo.workorderid "request id", max(wo.title) "subject", max(qd.queuename) "group", max(std.statusname) "request status", max(pd.priorityname) "priority", max(sdo.name) "site", max(wo.createdtime) ...