Version : 10.6

DB : MSSQL

OUTPUT :

SELECT tpr.PROJECTID ,
MAX(pr.TITLE) "Title",
(SELECT SUM(ct.TIMESPENT)/1000/3600 "Total Time Spent" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr1 ON tk.taskid=tpr1.taskid
LEFT JOIN ProjectDetails pr1 ON tpr.PROJECTID=pr1.PROJECTID
WHERE tk.MODULE IN ('project') and tpr.PROJECTID=tpr1.PROJECTID ) " project time spent",
(SELECT SUM(ct.TIMESPENT)/1000/3600 "Total Time Spent" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr2 ON tk.taskid=tpr2.taskid
LEFT JOIN ProjectDetails pr2 ON tpr.PROJECTID=pr2.PROJECTID
WHERE tk.MODULE IN ('milestone') and tpr.PROJECTID=tpr2.PROJECTID ) " Milestone time spent",
SUM(ct.TIMESPENT)/1000/3600 "Total Time Spent" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
WHERE tk.MODULE IN ('project', 'milestone')
GROUP BY tpr.PROJECTID
order by 1