select wo.workorderid "Request ID",
aau.FIRST_NAME "Requester",
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from chargestable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name
Timespent on worklogs under tasks
select wo.workorderid "Request ID",
aau.FIRST_NAME "Requester",
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from WorkOrder wo
LEFT JOIN workordertotaskdetails wotot ON wotot.workorderid=wo.workorderid
LEFT JOIN taskdetails tdet ON wotot.taskid=tdet.taskid
LEFT JOIN tasktocharge ttoch ON ttoch.taskid=tdet.taskid
LEFT JOIN chargestable ct ON ttoch.chargeid=ct.chargeid
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name
Timespent on worklogs under requests
select wo.workorderid "Request ID",
aau.FIRST_NAME "Requester",
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from WorkOrder wo
LEFT JOIN workordertocharge wotoc ON wotoc.workorderid=wo.workorderid
LEFT JOIN chargestable ct ON wotoc.chargeid=ct.chargeid
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name