SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request Status", LONGTODATE(ct.createdtime) AS "Last Worklog Added Time",taskdet.TITLE AS "Task Title",taskstatus.STATUSNAME AS "Task Status", taskowner.FIRST_NAME AS "Task Owner", taskdet.PER_OF_COMPLETION AS "Percentage Of Completion", c.comment "Task Comments", Max(Cast((((ct.timespent)/1000)/3600) as varchar(20)) +'Hrs '+cast(((((ct.timespent)/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((ct.timespent)%1000)%60) as varchar(20))+ 'Secs') "Time Spent", ct.description "Worklog comment" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN workordertotaskdetails wtd on wtd.workorderid=wo.workorderid LEFT JOIN taskdetails taskdet on taskdet.taskid=wtd.taskid LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID LEFT JOIN WorkOrder_Fields wof on wof.workorderid=wo.workorderid LEFT JOIN TaskToComment tskc ON tskc.taskid = taskdet.taskid LEFT JOIN Comments c ON tskc.commentid=c.commentid LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID WHERE (std.STATUSNAME!='Closed' AND std.STATUSNAME!='Close Requested') AND ct.chargeid=(SELECT max(chargeid) FROM workordertocharge where workorderid=wo.workorderid) GROUP BY Wo.workorderid,pd.PRIORITYNAME,cd.CATEGORYNAME,aaU.FIRST_NAME,ti.FIRST_NAME,QD.QUEUENAME, wotodesc.FULLDESCRIPTION,std.STATUSNAME,ct.createdtime,taskdet.TITLE,Wo.TITLE,taskstatus.STATUSNAME,taskowner.FIRST_NAME,taskdet.PER_OF_COMPLETION,c.comment, ct.description ORDER BY 1