Thank you. I was sure that it is impossible to do this with one select, but as I said it was almost 2 PM and I was not sure that I am not missing something.
At least I must create a temporary table with all dates with this range and then biuld select based on that table.
Thank you again for your efford. When I get home I will test your solution.


>>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
>
>Select cannot generate new data.
>
>This is a better output for me ( not tested ):
>

>SELECT date;
>,(SELECT COUNT(*) FROM table ;
>     WHERE Open_Date <= dates.Date AND (Close_Date IS NULL OR Close_Date BETWEEN @dBegin AND @dEnd)) BugsCount;
>from ;
>(SELECT DISTINCT CASE WHEN Open_Date>@dBegin THEN Open_Date ELSE @dBegin END date;
>	FROM table ;
>	WHERE Open_Date <= @dEnd AND (Close_Date IS NULL OR Close_Date BETWEEN @dBegin AND @dEnd)) dates
>
>-- OR
>SELECT date;
>,COUNT(*) BugsCount;
>from ;
>(SELECT DISTINCT CASE WHEN Open_Date>@dBegin THEN Open_Date ELSE @dBegin END date;
>	FROM table ;
>	WHERE Open_Date <= @dEnd AND (Close_Date IS NULL OR Close_Date BETWEEN @dBegin AND @dEnd)) dates;
>	JOIN table ON Open_Date <= dates.Date ;
>	WHERE Close_Date IS NULL OR Close_Date BETWEEN @dBegin AND @dEnd;
>GROUP BY 1
>