Novell Service Desk KPI queries / KPI reports

Need to run NSD KPI queries / KPI reports

Total Incidents:
The number of requests included in the date range queried.
(We are selecting all incidents that may be created and/or closed within the date range)

select count(*) from incident
where
((report_date >= '2010-10-01 00:00:00' and report_date <= '2010-10-31 23:59:99')
or
(close_date >= '2010-10-01 00:00:00' and close_date <= '2010-10-31 23:59:99'))
and
deleted = 0
order by report_date asc;

=======================================
Total New:
The number of newly created requests included in the date range queried.
(We are selecting all incidents created within the date range)

select count(*) from incident
where
report_date >= '2010-10-01 00:00:00' and report_date <= '2010-10-31 23:59:99'
and
deleted = 0
order by report_date asc;

=======================================
Total Resolved:
The number of requests resolved during the date range queried.
(We are selecting the subset of resolved requests from the main set of date in 'Total Incidents')

select count(*) from incident
where
deleted = 0 and
close_date >= open_date and
((report_date >= '2010-10-01 00:00:00' and report_date <= '2010-10-31 23:59:99')
or
(close_date >= '2010-10-01 00:00:00' and close_date <= '2010-10-31 23:59:99'))
order by report_date asc;

=======================================
Resolved Same Day:
The number of requests that were resolved on the same day that they were created. This is based on the calendar date of the original request, logged in the timezone of the server that hosts Novell Service Desk.
(We are looking for same day opened and closed incidents within the total incidents)

select count(*) from incident
where
date(close_date) = date(report_date) and
((report_date >= '2010-10-01 00:00:00' and report_date <= '2010-10-31 23:59:99')
or
(close_date >= '2010-10-01 00:00:00' and close_date <= '2010-10-31 23:59:99'))
and deleted = 0
order by report_date asc;

=======================================
Resolved On Spot:
The number of requests resolved within 10 minutes of being opened.
(We are looking for the resolved same day incidents but resolved within 10 mins of opening)

select count(*)
from incident
where
deleted = 0 and
datediff(close_date, open_date) = 0 and
timediff(close_date, open_date) <= '00:10:00' and
((report_date >= '2010-10-01 00:00:00' and report_date <= '2010-10-31 23:59:99')
or
(close_date >= '2010-10-01 00:00:00' and close_date <= '2010-10-31 23:59:99'))
order by report_date asc;

Formerly known as 1001421