Query Error (KPI report :based on group and technician)
This query doesn't work in build 8013 for MSSQL
(https://forums.manageengine.com/#Topic/49000005072090)
SELECT au.FIRST_NAME 'Technician', qd2.QUEUENAME "Group", (SELECT COUNT(wo4.WORKORDERID) FROM Workorder_Threaded wot4 INNER JOIN workorder wo4 ON wo4.WORKORDERID=wot4.WORKORDERID LEFT JOIN WorkOrderStates wos4 ON wos4.WORKORDERID=wo4.WORKORDERID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) AND wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> AND wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) 'Total Number of calls created',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID) )'Total Closed Calls', (SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '0' AND wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> AND wot3.THD_WOID=wot3.WORKORDERID AND wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'No: of calls Closed within SLA', (SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '1' AND wot3.THD_WOID=wot3.WORKORDERID AND wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> AND wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'No of calls Closed exceeding SLA',CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo.workorderid) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 WHERE ( std.STATUSNAME = 'Closed') and wot.THD_WOID=wot.WORKORDERID and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID) and wos.ISOVERDUE = '0' ) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN StatusDefinition std4 ON wos4.STATUSID=std4.STATUSID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID) and std4.STATUSNAME = 'Closed') ELSE 0 END '% of calls created & completed within sla', CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '1' and wot3.THD_WOID=wot3.WORKORDERID and wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> and wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE wos4.statusid=3 AND (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) ELSE 0 END '%of calls created and completed exceeding SLA' FROM WorkOrder_Threaded wot2 INNER JOIN WorkOrder wo2 ON wot2.WORKORDERID=wo2.WORKORDERID LEFT JOIN WorkOrderStates wos0 ON wos0.workorderid=wo2.workorderid LEFT JOIN AaaUser au ON au.user_id=wos0.ownerid LEFT JOIN WorkOrder_Queue woq2 ON wo2.WORKORDERID=woq2.WORKORDERID LEFT JOIN QueueDefinition qd2 ON woq2.QUEUEID=qd2.QUEUEID WHERE (wot2.THD_WOID=wot2.WORKORDERID) AND wos0.OWNERID IS NOT NULL and wo2.CREATEDTIME >= <from_thismonth> AND wo2.CREATEDTIME <= <to_thismonth> and au.FIRST_NAME is not null and qd2.QUEUENAME is not null GROUP BY qd2.QUEUENAME ,au.first_name,wos0.OWNERID, woq2.QUEUEID
(https://forums.manageengine.com/#Topic/49000005072090)
SELECT au.FIRST_NAME 'Technician', qd2.QUEUENAME "Group", (SELECT COUNT(wo4.WORKORDERID) FROM Workorder_Threaded wot4 INNER JOIN workorder wo4 ON wo4.WORKORDERID=wot4.WORKORDERID LEFT JOIN WorkOrderStates wos4 ON wos4.WORKORDERID=wo4.WORKORDERID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) AND wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> AND wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) 'Total Number of calls created',(SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID) )'Total Closed Calls', (SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '0' AND wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> AND wot3.THD_WOID=wot3.WORKORDERID AND wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'No: of calls Closed within SLA', (SELECT COUNT(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '1' AND wot3.THD_WOID=wot3.WORKORDERID AND wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> AND wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) 'No of calls Closed exceeding SLA',CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo.workorderid) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 WHERE ( std.STATUSNAME = 'Closed') and wot.THD_WOID=wot.WORKORDERID and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID) AND woq.QUEUEID=(woq2.QUEUEID) and wos.ISOVERDUE = '0' ) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN StatusDefinition std4 ON wos4.STATUSID=std4.STATUSID LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID) and std4.STATUSNAME = 'Closed') ELSE 0 END '% of calls created & completed within sla', CASE WHEN (SELECT COUNT(wo.WORKORDERID) FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.STATUSNAME = 'Closed' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> AND wos.OWNERID=(wos0.OWNERID)) >0 THEN (SELECT count(wo3.WORKORDERID) FROM WorkOrder_Threaded wot3 INNER JOIN WorkOrder wo3 ON wot3.WORKORDERID=wo3.WORKORDERID LEFT JOIN WorkOrderStates wos3 ON wo3.WORKORDERID=wos3.WORKORDERID LEFT JOIN StatusDefinition std3 ON wos3.STATUSID=std3.STATUSID LEFT JOIN WorkOrder_Queue woq3 ON wo3.WORKORDERID=woq3.WORKORDERID LEFT JOIN QueueDefinition qd3 ON woq3.QUEUEID=qd3.QUEUEID WHERE std3.STATUSNAME = 'Closed' AND wos3.ISOVERDUE = '1' and wot3.THD_WOID=wot3.WORKORDERID and wo3.CREATEDTIME >= <from_thismonth> AND wo3.CREATEDTIME <= <to_thismonth> and wos3.OWNERID=(wos0.OWNERID) AND woq3.QUEUEID=(woq2.QUEUEID)) * 100 / (select count(wo4.workorderid) from workorder_threaded wot4 INNER JOIN workorder wo4 on wo4.workorderid=wot4.workorderid left join workorderstates wos4 on wos4.workorderid=wo4.workorderid LEFT JOIN WorkOrder_Queue woq4 ON wo4.WORKORDERID=woq4.WORKORDERID LEFT JOIN QueueDefinition qd4 ON woq4.QUEUEID=qd4.QUEUEID WHERE wos4.statusid=3 AND (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) AND woq4.QUEUEID=(woq2.QUEUEID)) ELSE 0 END '%of calls created and completed exceeding SLA' FROM WorkOrder_Threaded wot2 INNER JOIN WorkOrder wo2 ON wot2.WORKORDERID=wo2.WORKORDERID LEFT JOIN WorkOrderStates wos0 ON wos0.workorderid=wo2.workorderid LEFT JOIN AaaUser au ON au.user_id=wos0.ownerid LEFT JOIN WorkOrder_Queue woq2 ON wo2.WORKORDERID=woq2.WORKORDERID LEFT JOIN QueueDefinition qd2 ON woq2.QUEUEID=qd2.QUEUEID WHERE (wot2.THD_WOID=wot2.WORKORDERID) AND wos0.OWNERID IS NOT NULL and wo2.CREATEDTIME >= <from_thismonth> AND wo2.CREATEDTIME <= <to_thismonth> and au.FIRST_NAME is not null and qd2.QUEUENAME is not null GROUP BY qd2.QUEUENAME ,au.first_name,wos0.OWNERID, woq2.QUEUEID