Project Time Tracking Report by Technician Per Task
I am creating the following query report to run weekly that will pull in that weeks' hours & minutes reported in the Project > Task work log. This was working until a technician had multiple entries in a week for one task. The error returned at that point was:
"
org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression at
..." followed by a whole lot of random
text.
I'm sure its the sub-query performing the
SUM(ct.TIMESPENT)
for Task Hours and the
ROUND(((ct.TIMESPENT)
for Task Minutes but I don't know how to fix this. Any help is greatly appreciated!!!
Here is my query:
SELECT projectdet.TITLE "Project Title",
taskdet.TITLE "Task Title",
tst.StatusName "Task Status",
taskownersdu.firstname "Technician",
LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Task Scheduled Start Time",
(SELECT SUM(ct.TIMESPENT)/1000/3600 FROM chargestable ct, tasktocharge tc where tc.Taskid = taskdet.TASKID and tc.Chargeid = ct.Chargeid) "Task Hours",
(SELECT ROUND(((ct.TIMESPENT)/1000)/60 % 60) FROM chargestable ct, tasktocharge tc where tc.Taskid = taskdet.TASKID and tc.Chargeid = ct.Chargeid) "Task Minutes"
FROM TaskDetails taskdet
RIGHT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet.PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
LEFT JOIN ProjectStatus tst on taskdet.Statusid = tst.STATUSID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN TaskToCharge ttc ON taskdet.TASKID=ttc.TASKID
LEFT JOIN ChargesTable ct ON ttc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID
WHERE LONGTODATE(ct.ts_starttime) >= <from_thisweek> AND LONGTODATE(ct.ts_endtime) <= <to_thisweek>
GROUP BY taskownersdu.firstname, taskdet.TASKID, projectdet.projectid,projectdet.projectid,projectstatus.statusname,taskowner.first_name,tst.statusname
ORDER BY taskownersdu.firstname,projectdet .PROJECTID