Query to get Project and milestone timespent in same query.
Version : 10.6
DB : MSSQL
OUTPUT :
![](https://desk.zoho.com:443/support/ImageDisplay?downloadType=uploadedFile&fileName=1683782302689.png&blockId=6b6ce70e6c55f4e3ffff3f36ccd2886b230bd882a70d1493&zgId=c065ea7ed2255947&mode=view)
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
New to ADSelfService Plus?