I've been asked it is possible to do this with ONE SELECT. First I answered NO, but here is about 2 PM and my mind doesn't works properly, so ask you :-)
Here the conditions:

The database is a bug tracking database.
Table has several columns: bugID, open_date, close_date
A bug is considered open on a given day if it's open date is on or before that day, and it's close date is on or after that day or it is NULL.
So the goal is to generate a record set to show the number of open bugs for a range of dates.
The ideal result set would have two columns: date and open bug count on that date.
Some data:

(all dates here are in German Format - dd.mm.yyyy)

BugId   Open_Date       Close_Date
1	01.01.2006	02.01.2006	
3	21.01.2006	NULL
4	05.01.2006	10.02.2006	
5	01.12.2005	NULL
7	13.12.2005	13.02.2006
8	12.02.2006	NULL
12	10.02.2006	10.02.2006
14	07.04.2006	NULL
20	18.11.2005	NULL
21	10.05.2006	NULL
22	15.02.2006	NULL
23	01.10.2004	03.02.2006	
24	31.12.2005	31.12.2005	
27	09.01.2005	09.01.2006	
32	01.01.2006	NULL
45	13.02.2006	NULL
55	12.12.2005	12.12.2005
61	31.12.2005	NULL
88	09.01.2006	09.01.2006
99	13.03.2006	NULL

So if the period is 01.04.2006 - 15.04.2006 desired result is:

Date                    BugsCount 
2006-04-01 00:00:00.000	        9
2006-04-02 00:00:00.000	        9
2006-04-03 00:00:00.000	        9
2006-04-04 00:00:00.000	        9
2006-04-05 00:00:00.000	        9
2006-04-06 00:00:00.000	        9
2006-04-07 00:00:00.000	       10
2006-04-08 00:00:00.000	       10
2006-04-09 00:00:00.000	       10
2006-04-10 00:00:00.000	       10
2006-04-11 00:00:00.000	       10
2006-04-12 00:00:00.000	       10
2006-04-13 00:00:00.000	       10
2006-04-14 00:00:00.000	       10
2006-04-15 00:00:00.000	       10

TIA