INFO on query
I'm trying to do a query to have the timespent on change for every technician
I noticed that when the worklog is not associated to a task but directly on the change I have some columns NULL
SELECT COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID"
, 'Change' "Module"
, CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title"
,qd.QUEUENAME "Group"
,stg.NAME "Stage"
,sts.STATUSNAME "Status"
,CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent"
,to_char(from_unixtime(max(ct.CREATEDTIME)/1000),'DD/MM/YYYY HH24:MI:SS') "Time Spent Created Time"
,rctd.FIRST_NAME "Time Spent Technician"
,ctd.NAME "Change Type"
,i.NAME "Impact"
,u.NAME "Uregency"
,cd.CATEGORYNAME "Category"
,sd.NAME "SubCategory"
,stg.NAME "Stage"
,sts.STATUSNAME "Status"
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 CategoryDefinition cd ON ch2.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition sd ON ch2.SUBCATEGORYID=sd.SUBCATEGORYID
LEFT JOIN QueueDefinition qd ON ch2.GROUPID=qd.QUEUEID
LEFT JOIN ChangetypeDefinition ctd ON ch2.CHANGETYPEID=ctd.CHANGETYPEID
LEFT JOIN ImpactDefinition i ON ch2.IMPACTID=i.IMPACTID
LEFT JOIN UrgencyDefinition u ON ch2.URGENCYID=u.URGENCYID
LEFT JOIN Change_StageDefinition stg ON ch2.WFSTAGEID=stg.WFSTAGEID
LEFT JOIN Change_StatusDefinition sts ON ch2.WFSTATUSID=sts.WFSTATUSID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND
ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2017-01-01 00:00:00') * 1000 AS BIGINT)
AND ch.CHANGEID=82
GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID),ch2.TITLE, rctd.FIRST_NAME, ctd.NAME, qd.QUEUENAME, stg.NAME, sts.STATUSNAME, cd.CATEGORYNAME, sd.NAME, i.NAME, u.NAME
my actual resul is this.
The worklogs associted directly to the change have some fields null, the first record is a sum of worklog associated to a task
Another minor issue is that the query dind't sum the record on TimeSpent that comes from task or change
Stefano