SELECT pr.problemid "Problem ID",max(pr.title) "Subject" ,max(prs.rootcause) "Root Cause",longtodate(max(pr.reportedtime))"Created Time",

CONVERT(varchar, sum(ct.timespent/1000) / 3600) + ':' + RIGHT('0' + CONVERT(varchar,sum(ct.timespent/1000) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar, sum(ct.timespent/1000) % 60), 2) "Time Spent in Hrs",max(au.first_name) "Technician" FROM chargestable ct inner join problemtocharge ptc ON ct.chargeid=ptc.chargeid LEFT JOIN problem pr ON ptc.problemid=pr.problemid LEFT JOIN aaauser au on ct.technicianid=au.user_id left join problemresolution prs on prs.problemid=pr.problemid LEFT JOIN CategoryDefinition cat on cat.categoryid=pr.categoryid left join sduser sd on pr.originatorid=sd.userid left join aaauser on aaauser.user_id=sd.userid 

group by pr.problemid