Query to show worklogs under change tasks

Query to show worklogs under change tasks

PGSQL:

SELECT ad.ORG_NAME AS "Account", tk.taskid "Task ID", COALESCE(ctk.CHANGEID , ch.CHANGEID) "Change ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Change' "Module",cast((sum(ct.TIMESPENT)/1000 * interval '1 second') as varchar) "Time Spent",rctd.FIRST_NAME "Technician" FROM ChargesTable ct
LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID
LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID
LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID
LEFT JOIN SDUser rcti ON ch.technicianid=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN AccountSiteMapping asm ON ch.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-12-30 00:00:00') * 1000 AS BIGINT)  GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,tk.taskid
          • 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 time spent added under home tasks.

            Please go to Reports-New Query Report and execute this query. SELECT tk.TASKID "Task ID",MAX(tk.TITLE) "Task Title", cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent ...
          • 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 ...
          • Instruction to Change Worklog owner under All Request, worklog owner From Tech A to Tech B

            Scenario      Worklogs assigned to Duplicate user / Incorrect user need to change all worklogs under request  to actual Technician.  Step 1 : Navigate to Report - > New Query Report and execute the below query.  Select workorder.workorderid ...
          • 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" ...