Seeing all the stuff from the answers to your challenge, compared to the cleaner thing you wrote here, I find just another proof that a solid UDF is a life-saver, T-SQL-wise.

I'm not sure how well this query will scale and as you've mentioned it is a little hard to grasp.

I think I would go with a variable which is initialized with the amount of bugs that were opened and not closed before the start date (or were not closed at all) and two GROUP BY queries for the report period - one for OpenedAt and one for ClosedAt. I would then iterate through the months in question and increment (or decrement) the variable accordingly.

My aim is to reduce the number of table-scans the DBMS performs and make the code easier to handle.

Ayende,

I may be getting too creative here but back in the days I was coding a lot of reports and was faced with a lot of problematic queries which resulted with bad execution plans. I think your query will require at least one or two index-scans per month in the report if not more.

So I was thinking of a solution which would limit the amount of scans (which cost more as the table becomes larger) and would also translate to coherent, intent revealing, code:

delegate Dictionary<DateTime, int> GetBugCountGroupedByMonths(string dateFieldToGroupBy);

    static void BugReport(DateTime startDate, DateTime endDate)

    {

        SqlConnection conn = new SqlConnection();


        GetBugCountGroupedByMonths getBugCountGroupedByMonths =

            delegate(string dateFieldToGroupBy)

                {

                    SqlCommand bugCountGroupedByMonths =

                        new SqlCommand(

                        string.Format("select YEAR({0}) As Year, MONTH({0}) As Month, count(Id) As BugCount from bugs where {0} >= @1 and {0} <= @2 group by YEAR({0}), MONTH({0})", dateFieldToGroupBy), conn);

                    bugCountGroupedByMonths.Parameters.Add(startDate);

                    bugCountGroupedByMonths.Parameters.Add(endDate);


                    Dictionary<DateTime, int> result = new Dictionary<DateTime, int>();

                    using (SqlDataReader reader = bugCountGroupedByMonths.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            result.Add(new DateTime((int) reader["Year"], (int) reader["Month"], 1), (int) reader["BugCount"]);

                        }

                    }

                    return result;

                };

        Dictionary<DateTime, int> bugsOpenedByMonth = getBugCountGroupedByMonths("Opendat");

        Dictionary<DateTime, int> bugsClosedByMonth = getBugCountGroupedByMonths("Closedat");


        SqlCommand getBugsThatWereOpenAtStartDate = new SqlCommand("select count(Id) from Bugs where Openedat < @1 and (Closedat is null or Closedat > @1)", conn);

        getBugsThatWereOpenAtStartDate.Parameters.Add(startDate);

        int bugsOpenDuringCurrentMonth = (int)getBugsThatWereOpenAtStartDate.ExecuteScalar();


        for (DateTime currentMonth = new DateTime(startDate.Year, startDate.Month, 1); 

            currentMonth <= endDate ; currentMonth = currentMonth.AddMonths(1))

        {

            int bugsOpenedDuringCurrentMonth = 0;

            bugsOpenedByMonth.TryGetValue(currentMonth, out bugsOpenedDuringCurrentMonth);

            int bugsClosedDuringCurrentMonth = 0;

            bugsClosedByMonth.TryGetValue(currentMonth, out bugsClosedDuringCurrentMonth);


            bugsOpenDuringCurrentMonth += bugsOpenedDuringCurrentMonth - bugsClosedDuringCurrentMonth;

        }

    }

This way may seem more complex but it does remove complexity from the execution plan of the DBMS and will probably perform and scale better.

I also think that your original query would be more intent revealing if it contained a nested select for counting the bugs instead of a join.

Ayende,

Dynamic SQL can be fun and harmless, SQL injection is something you should solve at the user input level...

If you look closer at the code there are actually 3 round-trips to the database. The rest of the code uses the in-memory dictionaries which contain up to N key-value-pairs which can hardly affect performance.

Outside T-SQL currentdate is a function. perhaps you should name the set returned from month range to aid in readability.

DECLARE @start DATETIME, @end DATETIME

SET @start = '2007-04-01'

set @end = '2007-10-01'

SELECT YEAR(Currentdate) Year,

         MONTH(Currentdate) Month,

         COUNT(Bug.Id) OpenedBugCount

FROM Dbo.MonthRange(@start,@end ) AS Dates

JOIN Bugs Bug ON

                ON YEAR(Dates.Currentdate) >= YEAR(Bug.Openedat)

              AND YEAR(Dates.Currentdate) <= YEAR(ISNULL(Bug.Closedat,Dates.Currentdate))

              AND MONTH(Dates.Currentdate) >= MONTH(Bug.Openedat)

              AND MONTH(Dates.Currentdate) < MONTH(ISNULL(Bug.Closedat,DateAdd(MONTH,1,Dates.Dates.Currentdate)))

GROUP BY YEAR(Dates.Currentdate),MONTH(Dates.Currentdate)

I made a simple solution for a similar problem before and it can be pretty simple.

I think we can focus on the start date for begining calculations as we are counting bugs opened and close the same month.

We can also limitate the range by defining OpenedAt >= start date and ClosedAt <= end date.

Then, if we use sum instead of count, we can make simple counts without using an alternate table for date ranges (and not to forget the months where there is no bug closed.

Create function fGetOpenedBugsByPeriod (

@dtBegin    smalldatetime

,@dtEnd     smalldatetime

)

RETURNS TABLE

AS

RETURN (

SELECT [Year] = Year(Openedat),

    [Month]     = Month(Openedat),

    [Opened Bugs] = 

        SUM (   CASE 

                WHEN Month(Openedat) = Month(DateEnd) AND Year(Closedat) = Year(Closedat) THEN 1 

                ELSE 0 

                END ),

    [Closed Next Month] = 

        SUM (   CASE 

                WHEN DateDiff(month,Openedat,Closedat) = 1 THEN 1 

                ELSE 0 

                END )

FROM dbo.Bugs

WHERE 1=1

AND Openedat >= @dtBegin

AND Closedat        <= @dtEnd

GROUP BY

Year(Openedat), Month(Openedat)

)

GO

With this system, we can easilly add another calculation directly in the same table,as here for exemple the number of bugs closed the month after.

Ayende,

I may be in error, but for me it works in all the cases, even if you work on a range greater than 2 years. I supposed we are talking about bugs opened and close during a same calendar month, not about bugs closed in less than 1 month since a date, did I make a misunderstanding? Thank you