MySQL Query - Assign Time Taken > 4 Hours
Hello,
I need to create a report for a KPI we plan to implement on our service desk, but I am having a problem getting the exact data I need.
I found a MySQL query on this forum that is almost correct but I dont know enough about the table structures to change the statement myself, please can someone help! :)
What I need is to change the where clause of the statement below to only show requests which have a pickup time greater than or equal to 4 hours, but also keep all the being pulled the report the same.
Any help would be greatly appreciated!
Query:
SELECT wo.WORKORDERID 'Request ID',ti.FIRST_NAME 'Technician',pd.priorityname 'Priority',aau.FIRST_NAME 'Requester',std.STATUSNAME 'Request Status',(select FROM_UNIXTIME(wohi.operationtime/1000) from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID')) 'Assigned Time',FROM_UNIXTIME(wo.CREATEDTIME/1000) 'Created Time',CONCAT(TIMEDIFF((select FROM_UNIXTIME(wohi.operationtime/1000) from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID')),FROM_UNIXTIME(wo.CREATEDTIME/1000)),' ') 'Pick up 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 SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND wo.CREATEDTIME >= UNIX_TIMESTAMP('2009-08-01 00:00:00')*1000 AND wo.CREATEDTIME <= UNIX_TIMESTAMP('2009-08-31 23:59:59')*1000 ORDER BY 1
I need to create a report for a KPI we plan to implement on our service desk, but I am having a problem getting the exact data I need.
I found a MySQL query on this forum that is almost correct but I dont know enough about the table structures to change the statement myself, please can someone help! :)
What I need is to change the where clause of the statement below to only show requests which have a pickup time greater than or equal to 4 hours, but also keep all the being pulled the report the same.
Any help would be greatly appreciated!
Query:
SELECT wo.WORKORDERID 'Request ID',ti.FIRST_NAME 'Technician',pd.priorityname 'Priority',aau.FIRST_NAME 'Requester',std.STATUSNAME 'Request Status',(select FROM_UNIXTIME(wohi.operationtime/1000) from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID')) 'Assigned Time',FROM_UNIXTIME(wo.CREATEDTIME/1000) 'Created Time',CONCAT(TIMEDIFF((select FROM_UNIXTIME(wohi.operationtime/1000) from workorderhistory wohi where wohi.historyid = (select min(wohd1.historyid) from workorderhistorydiff wohd1 inner join workorderhistory woh on woh.historyid=wohd1.historyid where woh.workorderid =wo.workorderid and wohd1.PREV_VALUE IS NULL and wohd1.CURRENT_VALUE IS NOT NULL AND wohd1.COLUMNNAME = 'OWNERID')),FROM_UNIXTIME(wo.CREATEDTIME/1000)),' ') 'Pick up 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 SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE wo.ISPARENT='1' AND wo.CREATEDTIME >= UNIX_TIMESTAMP('2009-08-01 00:00:00')*1000 AND wo.CREATEDTIME <= UNIX_TIMESTAMP('2009-08-31 23:59:59')*1000 ORDER BY 1