Hi,

 

I would like to have the same report like this one (but by group and not by technician).

can you help me?

Thanks

 

SELECT au.FIRST_NAME 'Technicien', (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 WHERE (wot4.THD_WOID=wot4.WORKORDERID) AND wo4.CREATEDTIME >= <from_lastmonth> AND wo4.CREATEDTIME <= <to_lastmonth> AND wos4.OWNERID=(wos0.OWNERID)) 'Total de requêtes reçues',(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 = 'terminée' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth> AND wos.OWNERID=(wos0.OWNERID))'Total des requêtes terminées', (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 WHERE std3.STATUSNAME = 'terminée' AND wos3.ISOVERDUE = '0' AND wo3.CREATEDTIME >= <from_lastmonth> AND wo3.CREATEDTIME <= <to_lastmonth> AND wot3.THD_WOID=wot3.WORKORDERID AND wos3.OWNERID=(wos0.OWNERID)) 'SLA respecté', (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 WHERE std3.STATUSNAME = 'terminée' AND wos3.ISOVERDUE = '1' AND wot3.THD_WOID=wot3.WORKORDERID AND wo3.CREATEDTIME >= <from_lastmonth> AND wo3.CREATEDTIME <= <to_lastmonth> AND wos3.OWNERID=(wos0.OWNERID)) 'SLA violé',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 = 'terminée' AND (wot.THD_WOID=wot.WORKORDERID) AND wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth> 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 WHERE ( std.STATUSNAME = 'terminée') and wot.THD_WOID=wot.WORKORDERID and wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth> AND wos.OWNERID=(wos0.OWNERID) 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 WHERE (wot4.THD_WOID=wot4.WORKORDERID) and wo4.CREATEDTIME >= <from_lastmonth> AND wo4.CREATEDTIME <= <to_lastmonth> and wos4.OWNERID=(wos0.OWNERID) and std4.STATUSNAME = 'terminée') ELSE 0 END '% SLA respecté'

 

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 WHERE (wot2.THD_WOID=wot2.WORKORDERID) AND wos0.OWNERID IS NOT NULL and wo2.CREATEDTIME >= <from_lastmonth> AND wo2.CREATEDTIME <= <to_lastmonth> and au.FIRST_NAME is not null GROUP BY au.first_name,wos0.OWNERID