>>>>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.
>
>I realized that my comment about SQL Server using or not any index to optimize query is irrelevant for '@FirstOfTheMonth + 0 BETWEEN StartDate And EndDate' expression because those checks performed on the column level so they will not be used to optimeze query anyway. You can add one more condition to your query to speed it up

WHERE (StartDate BETWEEN @FirstOfTheMonth And @LastOfTheMonth OR EndDate BETWEEN @FirstOfTheMonth And @LastOfTheMonth )

Why it's an OR condition (should be AND, I think). Should it be for all days, right? Where should I put it? Can you give me the whole query again, please. I have hard time thinking this late at night...