SELECT aau.FIRST_NAME AS "Requester",
max(wo.WORKORDERID) AS "Request ID",
rtdef.NAME AS "Request request type",
wo.TITLE AS "Subject",
wo.description ,
std.STATUSNAME AS "Request Status",
ti.FIRST_NAME AS "Assigned Technician",
Longtodate(wos.ASSIGNEDTIME) AS "Request assigned time",
Longtodate(wo.RESOLVEDTIME) AS "Resolved Time",
ct.SHORT_DESCRIPTION AS "Worklog Description",
Longtodate(ct.CREATEDTIME) As "Worklog added time",
CONVERT(VARCHAR(8), DATEADD(MILLISECOND, SUM(ct.TIMESPENT), 0), 108) "Work Log Time Spent",
ad.org_name AS "Account Name",
sdo.NAME AS "Site" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY aau.FIRST_NAME,rtdef.NAME,wo.TITLE,std.STATUSNAME,ti.FIRST_NAME,wos.ASSIGNEDTIME,wo.RESOLVEDTIME,wo.description,ad.org_name,sdo.NAME,ct.TIMESPENT,ct.SHORT_DESCRIPTION,ct.CREATEDTIME