Version : 14001

DB : PGSQL

OUTPUT:

SELECT ti.FIRST_NAME AS "Technician",
wo.WORKORDERID AS "Request ID",
ad.ORG_NAME AS "Account",
longtodate(wo.CREATEDTIME) AS "Created Time",
aau.FIRST_NAME AS "Requester",
wo.TITLE AS "Subject",
ti.FIRST_NAME AS "Technician",
lvd.LEVELNAME AS "Level",
std.STATUSNAME AS "Request Status",
wo.COMPLETEDTIME AS "Completed Time",
wos.LAST_TECH_UPDATE AS "Last Update Time",
wo.TIMESPENTONREQ AS "Time Elapsed",
longtodate(wo.DUEBYTIME) AS "DueBy Time",
longtodate(wo.FR_DUETIME) AS "Response DueBy Time",
tiau.first_name "Changed by",
wohd.columnname "changed column",
case when wohd.prev_value != '-1' then to_char(from_unixtime((cast(cast(wohd.prev_value as varchar) as bigint))/1000),'DD/MM/YYYY HH24:MI:SS') else '0' END "From Due by time",
case when wohd.current_value != '-1' then to_char(from_unixtime((cast(cast(wohd.current_value as varchar) as bigint))/1000),'DD/MM/YYYY HH24:MI:SS') else '0' END "To due by time" 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 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 LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
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 workorderhistory woh on wo.workorderid=woh.workorderid
left join workorderhistorydiff wohd on woh.historyid=wohd.historyid left join aaauser tiau on woh.operationownerid=tiau.user_id where wohd.columnname in ('duebytime','FR_DUETIME') and ( ( qd.QUEUENAME = 'EU Service Desk' ) AND ( ( ( wo.CREATEDTIME >= ('1675209600000') ) AND ( ( wo.CREATEDTIME != 0 ) AND ( wo.CREATEDTIME IS NOT NULL ) ) ) AND ( ( wo.CREATEDTIME <= ('1677628799999') ) AND ( ( ( wo.CREATEDTIME != 0 ) AND ( wo.CREATEDTIME IS NOT NULL ) ) AND ( wo.CREATEDTIME != -1 ) ) ) ) )  AND wo.ISPARENT='1' AND wo.IS_CATALOG_TEMPLATE='0' and (wo.ISPARENT='1') and tiau.first_name != 'system' ORDER BY 1