OUTPUT:

    

SELECT wo.WORKORDERID AS "Request ID",
LONGTODATE(wo.CREATEDTIME) AS "Open date",
pd.PRIORITYNAME AS "Priority",
std.STATUSNAME AS "Request Status",
qd.QUEUENAME AS "Group",
ti.FIRST_NAME AS "Technician",
LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Updated Time",
LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time",
rrs.RESOLUTION AS "Resolution",
rtdef.NAME AS "Request Type",
ad.ORG_NAME AS "Account",
sdo.NAME AS "Site",
mdd.MODENAME AS "Request Mode",
LONGTODATE(wo.RESPONDEDTIME) AS "First Reply Date",
LONGTODATE(wo.COMPLETEDTIME) AS "Closed Date",
case when (std.ISPENDING='0' AND wos.ISOVERDUE='1') THEN 1 ELSE NULL END "SLA Violated",
case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE NULL END "Response SLA violated",
cri.FIRST_NAME AS "Created By",
case when wo.RESPONDEDTIME > 0 then extract(epoch from(to_timestamp(wo.RESPONDEDTIME/1000) -  to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600 else 0 END "Response time difference",
case when wo.completedtime > 0 then (extract(epoch from(to_timestamp(wo.completedtime/1000) -  to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600) else 0 END "Solution time difference" FROM WorkOrder wo

LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID

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

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

LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID

LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID

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

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

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

LEFT JOIN itemdefinition idef ON wos.itemid=idef.itemid

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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID

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 RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID

LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID

LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID

LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID WHERE wo.ISPARENT='1'