Is this what you want?

SELECT MONTH(OpenedAt) AS 'Month',

Year(OpenedAt) AS 'Year', 

COUNT([Id]) AS 'Opened Bugs'

FROM Bugs

GROUP BY

MONTH(OpenedAt),Year(OpenedAt)

@Mark:

I think yours is calculation how many were OPENED during a month, not how many were OPEN.

@Ayende:

By the example you gave, it seams you want to count bugs that were open at the end of the month (so the third bug isn't calculated into July)

Here's a crude stored proc to create a yearly report with that logic.

CREATE FUNCTION dbo.GetMonthStart (@Year AS INT, @Month AS INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @Date DATETIME

SET @Date = CONVERT(DATETIME, '1/' + CONVERT(VARCHAR(2), @Month) + '/' + CONVERT(VARCHAR(4), @Year), 103)

RETURN @Date

END

CREATE FUNCTION dbo.GetNextMonthStart (@Year AS INT, @Month AS INT)

RETURNS DATETIME

AS

BEGIN

DECLARE @Date DATETIME

SET @Date = DATEADD(MM, 1, dbo.GetMonthStart(@Year, @Month))

RETURN @Date

END

CREATE PROCEDURE dbo.YearlyBugStatus (@Year AS INT)

AS

BEGIN

        SELECT 1 AS [Month], dbo.GetMonthStart(@Year, 1) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 1) AS NextMonthStartAt  INTO #Months

UNION   SELECT 2 AS [Month], dbo.GetMonthStart(@Year, 2) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 2) AS NextMonthStartAt

UNION   SELECT 3 AS [Month], dbo.GetMonthStart(@Year, 3) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 3) AS NextMonthStartAt

UNION   SELECT 4 AS [Month], dbo.GetMonthStart(@Year, 4) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 4) AS NextMonthStartAt

UNION   SELECT 5 AS [Month], dbo.GetMonthStart(@Year, 5) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 5) AS NextMonthStartAt

UNION   SELECT 6 AS [Month], dbo.GetMonthStart(@Year, 6) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 6) AS NextMonthStartAt

UNION   SELECT 7 AS [Month], dbo.GetMonthStart(@Year, 7) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 7) AS NextMonthStartAt

UNION   SELECT 8 AS [Month], dbo.GetMonthStart(@Year, 8) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 8) AS NextMonthStartAt

UNION   SELECT 9 AS [Month], dbo.GetMonthStart(@Year, 9) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 9) AS NextMonthStartAt

UNION   SELECT 10 AS [Month], dbo.GetMonthStart(@Year, 10) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 10) AS NextMonthStartAt

UNION   SELECT 11 AS [Month], dbo.GetMonthStart(@Year, 11) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 11) AS NextMonthStartAt

UNION   SELECT 12 AS [Month], dbo.GetMonthStart(@Year, 12) AS MonthStartAt, dbo.GetNextMonthStart(@Year, 12) AS NextMonthStartAt


SELECT 

        m.Month AS [Month], 

        @Year AS [Year], 

        (

            SELECT 

                    COUNT (b.Id)

            FROM    Bugs b

            WHERE   b.OpenedAt < m.NextMonthStartAt 

                AND (   b.ClosedAt >= m.NextMonthStartAt 

                    OR  b.ClosedAt IS NULL)

        ) AS [Bugs]

from 

        #Months m


DROP TABLE #Months

END

EXEC dbo.YearlyBugStatus 2007

As I said, it's crude. But works

The temp table is needed for "empty" months, and for keeping the NextMonthStart computation for once per month.

@Ken.

That's exactly right. But I read: "the number of opened bugs". So I was thinking about how many were opened during a month. Not the bugs that are still open.

The following is a bit of a hack, as you really need a UDF that'll generate the first day of each month that needs reporting. However, the idea is the same. That being said, I suspect that a cursor might well be faster.

with Data as (

select 1 Id, '2007-04-02' OpenedAt, '2007-09-01' ClosedAt union

select 2,    '2007-07-01',          NULL                  union

select 3,    '2007-07-01',          '2007-07-23'

)

select seq month,

    (

        select  count(*) 

        from    Data

        where   month(OpenedAt) <= seq and seq < coalesce(month(ClosedAt), 13)

    ) open_bugs

from (

select 4 seq union select 5 union select 6 union 

select 7 union select 8 union select 9 union select 10

) t

Assuming you're okay with NOT seeing months where there are no bugs:

SELECT

DATEPART(mm, [OpenedAt]) AS [Month],

DATEPART(yy, [OpenedAt]) AS [Year],

COUNT([Id]) AS [Opened Bugs]

FROM

[Bugs]

GROUP BY

DATEPART(mm, [OpenedAt]),

DATEPART(yy, [OpenedAt])

ORDER BY

DATEPART(mm, [OpenedAt]),

DATEPART(yy, [OpenedAt])

declare @StartDate datetime

declare @EndDate datetime

declare @MaxDate datetime

set @StartDate = '2007-04-01'

set @EndDate = '2007-10-01'

set @MaxDate = '2999-12-31'

declare @t table (Year int, Month int)

-- build table for reporting date range

declare @d datetime

set @d = @StartDate

while @d <= @EndDate

begin

insert @t (Year, Month) values (Year(@d), Month(@d))

set @d = dateadd(month, 1, @d)

end

-- create aggreate of open bugs per month/year

select

x.Month, x.Year, OpenedBugs = count(1)

from

(

-- use date range table to find all bugs that are opened on that month/year

-- definition of open is bug that is opened on a month and has not been closed

-- in that specific month

select t.Year, t.Month, b.Id

from

    @t t

    inner join Bugs b 

        on (t.Year between Year(b.OpenedAt) and Year(isnull(b.ClosedAt, @MaxDate)))

        and (t.Month between Month(b.OpenedAt) and Month(isnull(b.ClosedAt, @MaxDate)) - 1)

) x

group by

x.Month, x.Year

Here's my stab at it. If I "interpret" what I think your business case is, I think the sample result is off.

Here's the total of all open bugs at the end of a month (excludes bugs that are closed in the same month) which is what Ken is deriving:

WITH cal AS (SELECT CAST('2007-4-01' AS datetime) AS DateValue

                         UNION ALL

                         SELECT     DateValue + 1 AS Expr1

                         FROM         cal AS cal_2

                         WHERE     (DateValue + 1 < '2007-12-31'))

SELECT DISTINCT MONTH(cal_1.DateValue) AS M, YEAR(cal_1.DateValue) AS Y, COUNT(DISTINCT Bugs.ID) AS OpenedBugs

 FROM         Bugs INNER JOIN

                        cal AS cal_1 ON Bugs.OpenedAt < cal_1.DateValue

 WHERE     (Bugs.ClosedAt IS NULL) OR

                        (Bugs.ClosedAt > cal_1.DateValue) AND (MONTH(Bugs.ClosedAt) <> MONTH(cal_1.DateValue))

 GROUP BY MONTH(cal_1.DateValue), YEAR(cal_1.DateValue) OPTION (MAXRECURSION 0)

Results

Month Year OpenedBugs

4 2007 1

5 2007 1

6 2007 1

7 2007 2

8 2007 2

9 2007 1

10 2007 1

11 2007 1

12 2007 1

This shows all number of bugs that were open at some time during the month:

WITH cal AS (SELECT CAST('2007-04-01' AS datetime) AS DateValue

                         UNION ALL

                         SELECT     DateValue + 1 AS Expr1

                         FROM         cal AS cal_2

                         WHERE     (DateValue + 1 < '2007-12-31'))

SELECT DISTINCT MONTH(cal_1.DateValue) AS M, YEAR(cal_1.DateValue) AS Y, COUNT(DISTINCT Bugs.ID) AS OpenedBugs

 FROM         Bugs CROSS JOIN

                        cal AS cal_1

 WHERE     (cal_1.DateValue BETWEEN Bugs.OpenedAt AND Bugs.ClosedAt) OR

                        (Bugs.ClosedAt IS NULL) AND (cal_1.DateValue > Bugs.OpenedAt)

 GROUP BY MONTH(cal_1.DateValue), YEAR(cal_1.DateValue) OPTION (MAXRECURSION 0)

Results

Month Year OpenedBugs

4 2007 1

5 2007 1

6 2007 1

7 2007 3

8 2007 2

9 2007 2

10 2007 1

11 2007 1

12 2007 1

Isn't SQL cool?