Query to show both task comments and worklog comments

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" 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

          • Related Articles

          • 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 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: 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 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 ...
          • Request aging with recent worklog comments

            MSSQL: 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 "Subject",wodm.Dependsonid ...