Version : 10609

DB : MSSQL & PGSQL

OUTPUT :

     

SELECT wo.WORKORDERID AS "Request ID",
LONGTODATE(wo.createdtime)  AS "CREATEDTIME",
sisd.STATUSNAME as "Changed From",
sinsd.STATUSNAME as "Changed To",
aaa.first_name as "Created By" FROM WorkOrder wo
LEFT JOIN WO_Assessment woas ON wo.WORKORDERID = woas.WORKORDERID
LEFT JOIN WO_Status_Info si ON woas.ASSESSMENTID = si.ASSESSMENTID
LEFT JOIN StatusDefinition sisd ON si.STATUSID = sisd.STATUSID
LEFT JOIN StatusDefinition sinsd ON si.NEXTSTATUSID = sinsd.STATUSID
LEFT JOIN workorderaccountmapping woac ON wo.WORKORDERID = woac.WORKORDERID
LEFT JOIN accountdefinition ad ON woac.accountid = ad.org_id
LEFT JOIN AAAUser aaa ON wo.REQUESTERID = aaa.USER_ID
LEFT JOIN WorkOrderstates wos ON wo.WORKORDERID = wos.WORKORDERID WHERE(wo.ISPARENT = '1' and sisd.STATUSNAME = 'Rejected' and si.NEXTSTATUSID IS Not Null and aaa.first_name in ('administrator') and wo.createdtime > <from_thismonth> and wo.createdtime < <to_thismonth> ) ORDER BY 1,woas.ASSESSMENTID asc