>>>You can try something like

DECLARE @FirstOfTheMonth datetime
>>>DECLARE @LastOfTheMonth datetime
>>>
>>>SET @FirstOfTheMonth = '2003/04/01'
>>>SET @LastOfTheMonth = DATEADD(day,-1, DATEADD(month, 1, @FirstOfTheMonth))
>>>
>>>SELECT
>>>		SUM( CASE WHEN StartDate <= @FirstOfTheMonth + 0 And EndDate >= @FirstOfTheMonth + 0
>>>				THEN 1 ELSE 0 END ) AS day01
>>>		SUM( CASE WHEN StartDate <= @FirstOfTheMonth + 1 And EndDate >= @FirstOfTheMonth + 1
>>>				THEN 1 ELSE 0 END ) AS day02
>>>...
>>>
>>>		SUM( CASE WHEN StartDate <= @FirstOfTheMonth + 27 And EndDate >= @FirstOfTheMonth + 27
>>>				THEN 1 ELSE 0 END ) AS day28
>>>
>>>		SUM( CASE WHEN StartDate <= @FirstOfTheMonth + 28 And EndDate >= @FirstOfTheMonth + 28
>>>					And @FirstOfTheMonth + 28 <= @LastOfTheMonth
>>>				THEN 1 ELSE 0 END ) AS day29
>>>...

>>
>>Great and simple. I was thinking about running a select statement inside a loop...
>
>You may also try

 ... WHEN @FirstOfTheMonth + 0 BETWEEN StartDate And EndDate
>-- instead of
> ... WHEN StartDate <= @FirstOfTheMonth + 0 And EndDate >= @FirstOfTheMonth + 0
>

I'm not sure if SQL Server could use any index to optimize query in this case.
=========
Why do you have only 29 days and why you have slightly different condition for the last day?

I agree about using between... Funny, we're usually have something like
field between FixedDay1 and FixedDay2, so it's hard to understand in the other way around, though should work as well.

Thanks again.