Query to show last comments added in Projects task_MSSQL

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
          • Related Articles

          • Query to show both task comments and worklog comments

            MSSQL: 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" ...
          • Query to show Last added worklog of a ticket _MSSQL

            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", wotodesc.FULLDESCRIPTION AS ...
          • Query to show the last worklog added in a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query report to show Problem fields along with last added notes

            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", LONGTODATE(prob.CLOSEDTIME) AS "Closed Date", ad.ORG_NAME AS ...
          • Query to show Last added worklog of a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...