I am working a custom query to create a Monthly Summary Report to include the following.

 

Count of Open Request

Count of Closed Request

Count of Calls Exceeded SLA

Count of Calls Within SLA

% Calls Exceeded SLA

% Of Calls Within SLA

 

Filter criteria - Created Time = Last month

Advanced Filtering - Group "is not" "Department1", "Department2"

 

Here is what I have so far.  The query works, but I need to apply the filters to not have a hard date and to filter out other departments.

 

 

select sum(case when sdf.statusid=1 then 1 else 0 end) 'Count Of Open Request' ,sum(case when  sdf.statusid=3 then 1 else 0 end) 'Count Of Closed Request', sum(case(isoverdue) when 1 then 1 else 0 end) 'Count Of calls Exceeded SLA',sum(case(isoverdue) when 0 then 1 else 1 end) 'Count Of calls within SLA' , (sum(case(isoverdue) when 1 then 1 else 0 end)*100)/count(wo.workorderid) "% Calls Exceeded SLA",((count(wo.workorderid) - sum(case(isoverdue) when 1 then 1 else 0 end))*100)/count(wo.workorderid) "% Of Calls Within SLA" from workorder wo
left join workorderstates wos on wo.workorderid = wos.workorderid
left join statusdefinition sdf on wos.statusid=sdf.statusid
 where wo.CREATEDTIME >= UNIX_TIMESTAMP('2013-05-01 00:00:00')*1000 AND wo.CREATEDTIME <= UNIX_TIMESTAMP('2013-05-31 23:59:59')*1000