SQL Challenge, getting historical datasolution
time to read 2 min | 349 words

As it turned out, this isn't that hard, all I needed to do was remember my trusty DateRange function, modify it to on months instead of days, and it was off to the races. This type of code does make my head hurt a tiny bit, it packs a lot into it.

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 )
         JOIN Bugs Bug
           ON YEAR(Currentdate) >= YEAR(Bug.Openedat)
              AND MONTH(Currentdate) >= MONTH(Bug.Openedat)
              AND YEAR(Currentdate) <= YEAR(ISNULL(Bug.Closedat,Currentdate))
              AND MONTH(Currentdate) < MONTH(ISNULL(Bug.Closedat,DateAdd(MONTH,1,Currentdate)))
GROUP BY YEAR(Currentdate),MONTH(Currentdate)

I would get it into NHibernate on Sunday, should be fun, it is about the ninth select is the super report... :-)

0 comments
Tags:

Related posts that you may find interesting:

22 Jun 2006 Solving a Tricky SQL Problem
Programming Databases SQL
07 Oct 2006 SQL Refactor
Databases SQL Reviews
17 Mar 2007 Performance: Multiply Collections Fetch With NHibernate
NHibernate

More posts in "SQL Challenge, getting historical data" series:

  1. (12 Aug 2007) Performance characteristics
  2. (10 Aug 2007) solution