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