Updation / modification of provided query
Hi,
Could you please make some changes in the below provided query or give a new query according to my requirement:-
select wo.WORKORDERID 'Request ID', TITLE 'Title', DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%d-%m-%Y %k:%i') 'Created Time', DATE_FORMAT(FROM_UNIXTIME(wo.COMPLETEDTIME/1000) ,'%d-%m-%Y %k:%i') 'Completed Time', MODENAME 'Mode', STATUSNAME 'Status', aau.FIRST_NAME 'Technician', DATE_FORMAT(FROM_UNIXTIME(woh.OPERATIONTIME/1000) ,'%d-%m-%Y %k:%i') 'Operation Time',woh.OPERATION,aau1.FIRST_NAME 'Modified By' from WorkOrder wo LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition sd on wos.STATUSID = sd.STATUSID LEFT JOIN SDUser sdu on wos.OWNERID = sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN SDUser sdu1 ON sdu1.USERID = woh.OPERATIONOWNERID LEFT JOIN AaaUser aau1 ON sdu1.USERID = aau1.USER_ID where woh.OPERATION like '%PDATE' and ( wo.CREATEDTIME >= (UNIX_TIMESTAMP(DATE('2006-09-14 00:00:00')) * 1000) and wo.CREATEDTIME <= (UNIX_TIMESTAMP(DATE('2006-09-23 00:00:00')) * 1000) ) order by 7
I want to generate report which shows time spent or calculate time from first updatation/modification in the request/recode to close that call/request.
Please help!!!
Regards,
Trisha
Could you please make some changes in the below provided query or give a new query according to my requirement:-
select wo.WORKORDERID 'Request ID', TITLE 'Title', DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%d-%m-%Y %k:%i') 'Created Time', DATE_FORMAT(FROM_UNIXTIME(wo.COMPLETEDTIME/1000) ,'%d-%m-%Y %k:%i') 'Completed Time', MODENAME 'Mode', STATUSNAME 'Status', aau.FIRST_NAME 'Technician', DATE_FORMAT(FROM_UNIXTIME(woh.OPERATIONTIME/1000) ,'%d-%m-%Y %k:%i') 'Operation Time',woh.OPERATION,aau1.FIRST_NAME 'Modified By' from WorkOrder wo LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition sd on wos.STATUSID = sd.STATUSID LEFT JOIN SDUser sdu on wos.OWNERID = sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN SDUser sdu1 ON sdu1.USERID = woh.OPERATIONOWNERID LEFT JOIN AaaUser aau1 ON sdu1.USERID = aau1.USER_ID where woh.OPERATION like '%PDATE' and ( wo.CREATEDTIME >= (UNIX_TIMESTAMP(DATE('2006-09-14 00:00:00')) * 1000) and wo.CREATEDTIME <= (UNIX_TIMESTAMP(DATE('2006-09-23 00:00:00')) * 1000) ) order by 7
I want to generate report which shows time spent or calculate time from first updatation/modification in the request/recode to close that call/request.
Please help!!!
Regards,
Trisha
