What?! Of course SQL can generate data! You just have to ask nicely. :)

-- Sample dates here
declare @dBegin datetime, @dEnd datetime
select @dBegin = '2006-01-01 00:00:00.000', 
       @dEnd   = @dBegin + 50

-- Sample data table here
create table #bugs (bugid int, open_date datetime, close_date datetime)
insert into #bugs values (1, '2006-01-01 00:00:00.000', null)
insert into #bugs values (2, '2006-02-02 00:00:00.000', null)
insert into #bugs values (3, '2006-02-02 00:00:00.000', null)
insert into #bugs values (4, '2006-02-02 00:00:00.000', null)
insert into #bugs values (5, '2006-03-03 00:00:00.000', null)

-- Single select statement here
select r.range_date, isnull(b.bug_count,0) bug_count
from (select open_date, count(1) bug_count
	  from #bugs
	  group by open_date) b
right outer join (
select @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 range_date
from	  (select 0 b1  union select 1    b1)  t1
cross join (select 0 b2  union select 2    b2)  t2
cross join (select 0 b3  union select 4    b3)  t3
cross join (select 0 b4  union select 8    b4)  t4
cross join (select 0 b5  union select 16   b5)  t5
cross join (select 0 b6  union select 32   b6)  t6
cross join (select 0 b7  union select 64   b7)  t7
cross join (select 0 b8  union select 128  b8)  t8
cross join (select 0 b9  union select 256  b9)  t9
cross join (select 0 b10 union select 512  b10) t10
cross join (select 0 b11 union select 1024 b11) t11
cross join (select 0 b12 union select 2048 b12) t12
where @dBegin + b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12 <= @dEnd) r
on b.open_date = r.range_date
order by r.range_date

-- Cleanup sample data
drop table #bugs

This routine should work for date ranges up to 4095 days apart (and can easily be increased if warranted). You'll notice that I'm performing a somewhat elaborate bit integration, constructing my own, run-time-generated table of integers, from which I'm selecting as many as I need for the specified date range. That in turn allows me to generate on the fly a list of all the dates I wish to look at -- irrespective of what my data table may or may not contain.

Whilst generating this table of dates, I simply cross-reference with the actual data summary, and produce the result. And this was all with a single (though somewhat hideous-looking) select statement. I included some sample data here with date expressions that work in both English and German in case anybody wants to try it out.

- Robert Schwartz