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

no TRUE,
On relational algebra you cannot declare a SELECT without a source domain data.

on mssql,
the (SELECT 0) is a short syntax of the (SELECT 0 FROM oneRowTable),
where oneRowTable is a MSSQL constant table.
This is the execution plan:

-- execute this
SET SHOWPLAN_TEXT ON 
select 0

-- return the scan of a constant table
  |--Constant Scan  

then the SELECT doens't generate data, because data exists within the system table.


>

>-- 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
>

the cross cartesian number composition is good, but
- union distinct is wrong
- better making the table independent from external values,
in this way a MSSQL function can be built that returns the table in a certain range
dbo.fn_integers(@start,@end,@step)

-- Sample dates here
declare @dBegin datetime, @dEnd datetime
select @dBegin = '20060101', 
       @dEnd   = @dBegin + 50

-- Sample data table here
create table #bugs (bugid int, open_date datetime, close_date datetime)
insert into #bugs values (1, '20060101', null)
insert into #bugs values (2, '20060202', null)
insert into #bugs values (3, '20060202', null)
insert into #bugs values (4, '20060202', null)
insert into #bugs values (5, '20060303', null)

-- Single select statement here
select @dBegin+r.range, isnull(b.bug_count,0) bug_count
from (select open_date, count(*) bug_count
	  from #bugs
	  group by open_date) b
right outer join
(select t0.b + t1.b + t2.b + t3.b +t4.b +t5.b +t6.b +t7.b +t8.b +t9.b +t10.b +t11.b range
from	   (select 0 b union all select    1)  t0
cross join (select 0 b union all select    2)  t1
cross join (select 0 b union all select    4)  t2
cross join (select 0 b union all select    8)  t3
cross join (select 0 b union all select   16)  t4
cross join (select 0 b union all select   32)  t5
cross join (select 0 b union all select   64)  t6
cross join (select 0 b union all select  128)  t7
cross join (select 0 b union all select  256)  t8
cross join (select 0 b union all select  512)  t9
cross join (select 0 b union all select 1024) t10
cross join (select 0 b union all select 2048) t11 ) r
on b.open_date = @dBegin + r.range
WHERE r.range <= @dEnd - @dBegin
order by r.range

-- Cleanup sample data
drop table #bugs

with a function it become

...
right outer join dbo.ft_Integers(0,@dEnd - @dBegin) r 
on b.open_date = @dBegin + r.range
order by r.range

>
>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