SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "شناسه درخواست",
CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "عنوان کار",
max(aau.FIRST_NAME) "درخواست کننده",
max(taskcreatedby.FIRST_NAME) "ایجاد شده توسط",
max(taskowner.FIRST_NAME) "مالک",
max(sdo.NAME) "شعبه" ,
TO_CHAR((sum(ct.TIMESPENT)/1000 || ' second')::interval, 'HH24:MI:SS') "زمان صرف شده" FROM ChargesTable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN AaaUser taskcreatedby ON tk.CREATEDBY=taskcreatedby.USER_ID
LEFT JOIN SDUser taskownersdu ON tk.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN QueueDefinition taskgroup ON tk.GROUPID=taskgroup.QUEUEID
LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND tk.ACTUALENDTIME >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2014-01-01 00:00:00') * 1000 AS BIGINT) AND tk.ACTUALENDTIME <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT)
GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid) ORDER BY 1