SELECT wo.WORKORDERID 'RequestID',

max(ti.FIRST_NAME) 'Technician Name',

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')'Created Time',

(case when max(wo.respondedtime)!=0 then dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(wo.respondedtime/1000),'1970-01-01 00:00:00') else NULL end) 'Responded Time',

(case when max(wo.completedtime)!=0 then dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(wo.completedtime/1000),'1970-01-01 00:00:00') else NULL end) 'Completed Time',

(select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(woh.operationtime/1000),'1970-01-01 00:00:00') from workorderhistory woh where woh.workorderid=wo.workorderid and woh.operation='RESOLVED' ) 'Resolved Time',

max(wo.TITLE) 'Subject',

max(sdo.NAME) 'Site',

max(aau.FIRST_NAME) 'Requester',

max(qd.QUEUENAME) 'Group',

max(case when wos.isoverdue=1 then'violated' else 'Not violated' end)'SLA Violation' FROM WorkOrder wo 

LEFT JOIN workorderhistory woh on  wo.workorderid = woh.workorderid

LEFT JOIN workorderhistorydiff wohd on wohd.historyid=woh.historyid

LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 

LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID 

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 

LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 

LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 

LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 

LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 

LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID 

WHERE (wo.ISPARENT = 1) 

and (dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2012-07-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2012-08-31 23:59',21)) group by wo.workorderid ORDER BY 1