Version : 10609

DB : MSSQL & PGSQL

OUTPUT :

 

SELECT wo.WORKORDERID "Request ID",
Longtodate(max(wo.CREATEDTIME)) "Created Time",
longtodate(min(woa.executedtime)) "Assigned time",
max(aau.FIRST_NAME) "Requester",
max(wo.TITLE) "Subject",
max(qd.QUEUENAME) "Group",
max(au.first_name) "Technician",
max(ti.FIRST_NAME) "Current Technician",
max(pd.PRIORITYNAME) "Priority",
max(cd.CATEGORYNAME) "Category",
max(scd.NAME) "Subcategory",
max(std.STATUSNAME) "Request Status"
FROM WorkOrder wo
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
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
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WO_Assessment woa ON wo.WORKORDERID=woa.WORKORDERID
left JOIN WO_Tech_Info wti ON woa.ASSESSMENTID=wti.ASSESSMENTID
LEFT JOIN AaaUser au ON wti.TECHNICIANID=au.USER_ID
WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth> GROUP BY wo.WORKORDERID ORDER BY 1