Version : 13000

DB : PGSQL

Query:

SELECT wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", aau.FIRST_NAME AS "Requester", longtodate(wo.COMPLETEDTIME) AS "Closed On", ad.ORG_NAME AS "Account", rtl.TEMPLATENAME AS "Template Name", longtodate(wos.ASSIGNEDTIME) AS "Assigned Time", ti.FIRST_NAME AS "Assigned To", (extract(epoch from(to_timestamp(woh.operationtime/1000) -  to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600) AS "Time Elapse between INC & Req" FROM WorkOrder wo 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 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 RequestTemplate_List rtl ON wo.TEMPLATEID=rtl.TEMPLATEID LEFT JOIN workorderhistory woh ON wo.workorderid=woh.workorderid
LEFT JOIN workorderhistorydiff wohd ON woh.historyid=wohd.historyid  WHERE (wo.ISPARENT='1') and (wohd.columnname='IS_CATALOG_TEMPLATE') and wo.CREATEDTIME >= <from_lastmonth> and wo.CREATEDTIME <= <to_lastmonth> order by wo.CREATEDTIME