>>>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
>>
>> ... 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 )
--sb--