I am trying to run a report to get a complete history of the last years jobs, so can see when jobs were on hold, pending third party etc with timestamps.  I have found the query below which looks like it might do the trick but get a java.sql.SQLException: Incorrect syntax near the keyword 'TRUNCATE'.

 

select wo.WORKORDERID 'Request ID', (select CASE WHEN wohd.COLUMNNAME = 'OWNERID' THEN 'Technician' ELSE 'GROUP' END ) 'Changes in',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%d-%m-%Y %k:%i') "Created Time",(select CASE WHEN wo.COMPLETEDTIME = 0 THEN 'Not Closed' ELSE DATE_FORMAT(FROM_UNIXTIME(wo.COMPLETEDTIME/1000) ,'%d-%m-%Y %k:%i') END) "Closed Time",(select CASE WHEN wo.COMPLETEDTIME = 0 THEN '-' ELSE TRUNCATE( (wo.TIMESPENTONREQ/1000)/3600,2) END) 'Time Spent (Hrs)', COALESCE((select QUEUENAME from QueueDefinition where wohd.COLUMNNAME = 'QUEUEID' and ( wohd.PREV_VALUE = QUEUEID) ), (select aau.FIRST_NAME from AaaUser aau where wohd.COLUMNNAME = 'OWNERID' and wohd.PREV_VALUE = aau.USER_ID)) 'Assign From',COALESCE((select QUEUENAME from QueueDefinition where wohd.COLUMNNAME = 'QUEUEID' and ( wohd.CURRENT_VALUE = QUEUEID) ), (select aau.FIRST_NAME from AaaUser aau where wohd.COLUMNNAME = 'OWNERID' and wohd.CURRENT_VALUE = aau.USER_ID)) 'Assign To' from WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID = wo.WORKORDERID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID where wohd.COLUMNNAME in ( 'QUEUEID', 'OWNERID' ) and (wot.THD_WOID = wot.WORKORDERID) and wo.CREATEDTIME >= (UNIX_TIMESTAMP(DATE('2007-01-01 00:00:00')) * 1000) and (wo.CREATEDTIME <= (UNIX_TIMESTAMP(DATE('2007-04-23 00:00:00')) * 1000)) and wohd.PREV_VALUE IS NOT NULL

 

select wo.WORKORDERID 'Request ID',woh.OPERATION, COALESCE((select STATUSNAME from StatusDefinition where wohd.COLUMNNAME = 'STATUSID' and ( wohd.PREV_VALUE = STATUSID) ), (select aau.FIRST_NAME from AaaUser aau where wohd.COLUMNNAME = 'OWNERID' and wohd.PREV_VALUE = aau.USER_ID)) 'Assign From',COALESCE((select STATUSNAME from StatusDefinition where wohd.COLUMNNAME = 'STATUSID' and ( wohd.CURRENT_VALUE = STATUSID) ), (select aau.FIRST_NAME from AaaUser aau where wohd.COLUMNNAME = 'OWNERID' and wohd.CURRENT_VALUE = aau.USER_ID)) 'Assign To' from WorkOrder wo LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID where wohd.COLUMNNAME in ( 'STATUSID', 'OWNERID' )

Regards

MWHEELER