Hi All, I have the below query for showing the technician KPI for each month ( from this month to this month)

SELECT au.FIRST_NAME 'Technician', (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_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> AND wos4.OWNERID=(wos0.OWNERID)) '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  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))'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   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)) '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      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)) '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    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 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_thismonth> AND wo4.CREATEDTIME <= <to_thismonth> and wos4.OWNERID=(wos0.OWNERID) 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  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)) * 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 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)) 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  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  GROUP BY au.first_name,wos0.OWNERID

I would like to be able to see the KPI stats for last month or the last year. or the year so far.

could you let me know an easy way of changing the report to show this

thanks for your time

Russ