Calculating Open incidents per month

We have Incidents in our system with Start Time and Finish Time and project name (and other info) . We would like to have report: How many Incidents has 'open' status per month per project. Open status mean: Not finished.

If incident is created in December 2009 and closed in March 2010, then it should be included in December 2009, January and February of 2010.

开发者_高级运维Needed structure should be like this:

Project   Year    Month     Count
-------  ------  -------   -------
Test       2009   December   2
Test       2010   January     10
Test       2010   February    12
....

In SQL Server:

SELECT
  Project,
  Year = YEAR(TimeWhenStillOpen),
  Month = DATENAME(month, MONTH(TimeWhenStillOpen)),
  Count = COUNT(*)
FROM (
  SELECT
    i.Project,
    i.Incident,
    TimeWhenStillOpen = DATEADD(month, v.number, i.StartTime)
  FROM (
    SELECT
      Project,
      Incident,
      StartTime,
      FinishTime = ISNULL(FinishTime, GETDATE()),
      MonthDiff = DATEDIFF(month, StartTime, ISNULL(FinishTime, GETDATE()))
    FROM Incidents
  ) i
    INNER JOIN master..spt_values v ON v.type = 'P'
      AND v.number BETWEEN 0 AND MonthDiff - 1
) s
GROUP BY Project, YEAR(TimeWhenStillOpen), MONTH(TimeWhenStillOpen)
ORDER BY Project, YEAR(TimeWhenStillOpen), MONTH(TimeWhenStillOpen)

Briefly, how it works:

• The most inner subselect, that works directly on the Incidents table, simply kind of 'normalises' the table (replaces NULL finish times with the current time) and adds a month difference column, MonthDiff. If there can be no NULLs in your case, just remove the ISNULL expression accordingly.

• The outer subselect uses MonthDiff to break up the time range into a series of timestamps corresponding to the months where the incident was still open, i.e. the FinishTime month is not included. A system table called master..spt_values is also employed there as a ready-made numbers table.

• Lastly, the main select is only left with the task of grouping the data.

A useful technique here is to create either a table of "all" dates (clearly that would be infinite so I mean a sufficiently large range for your purposes) OR create two tables: one of all the months (12 rows) and another of "all" years.

Let's assume you go for the 1st of these:

create table all_dates (d date)

and populate as appropriate. I'm going to define your incident table as follows

create table incident
(
    incident_id  int not null,
    project_id   int not null,
    start_date   date not null,
    end_date     date null
)

I'm not sure what RDBMS you are using and date functions vary a lot between them so the next bit may need adjusting for your needs.

select
  project_id,
  datepart(yy, all_dates.d) as "year",
  datepart(mm, all_dates.d) as "month",
  count(*) as "count"
from
  incident,
  all_dates
where
  incident.start_date <= all_dates.d and
  (incident.end_date >= all_dates.d or incident.end_date is null) 
group by 
  project_id,
  datepart(yy, all_dates.d) year,
  datepart(mm, all_dates.d) month

That is not going to quite work as we want as the counts will be for every day that the incident was open in each month. To fix this we either need to use a subquery or a temporary table and that really depends on the RDBMS...

Another problem with it is that, for open incidents it will show them against all future months in your all_dates table. adding a all_dates.d <= today solves that. Again, different RDBMSs have different methods of giving back now/today/systemtime...

Another approach is to have an all_months rather than all_dates table that just has the date of first of the month in it:

create table all_months (first_of_month date)

select
  project_id,
  datepart(yy, all_months.first_of_month) as "year",
  datepart(mm, all_months.first_of_month) as "month",
  count(*) as "count"
from
  incident,
  all_months
where
  incident.start_date <= dateadd(day, -1, dateadd(month, 1, first_of_month)
  (incident.end_date >= first_of_month or incident.end_date is null) 
group by 
  project_id,
  datepart(yy, all_months.first_of_month),
  datepart(mm, all_months.first_of_month)