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 >>...>
... WHEN @FirstOfTheMonth + 0 BETWEEN StartDate And EndDate -- instead of ... WHEN StartDate <= @FirstOfTheMonth + 0 And EndDate >= @FirstOfTheMonth + 0I'm not sure if SQL Server could use any index to optimize query in this case.