Request ID Technician Priority Subject Requester Group Site Request Status Operation Time Created Time UnAssigned Time Completed Time
MY-SQL: Un-Assigned Tickets: Time Taken to Pickup
SELECT wo.WORKORDERID 'Request ID',ti.FIRST_NAME 'Technician',pd.priorityname 'Priority',wo.TITLE 'Subject',aau.FIRST_NAME 'Requester',qd.QUEUENAME 'Group',sdo.NAME 'Site',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')) 'Operation 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)),' ') 'UnAssigned Time',FROM_UNIXTIME(wo.COMPLETEDTIME/1000) 'Completed 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' ORDER BY 1
MS-SQL: Un-Assigned Tickets: Time Taken to Pickup
SELECT wo.WORKORDERID 'Request ID',ti.FIRST_NAME 'Technician',pd.priorityname 'Priority',wo.TITLE 'Subject',aau.FIRST_NAME 'Requester',qd.QUEUENAME 'Group',sdo.NAME 'Site',std.STATUSNAME 'Request Status',(select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wohi.operationtime/1000),'1970-01-01 00:00:00') 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')) 'Operation Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time',convert(varchar,ROUND(((((select wohi.operationtime 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'))-wo.CREATEDTIME)/1000)/3600),2)) +':'+ convert(varchar,ROUND((((((select wohi.operationtime 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'))-wo.CREATEDTIME)/1000)/60)) % 60,2)) 'UnAssigned Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00') 'Completed 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' ORDER BY 1
regards,
Rachana