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 + 0 >I'm not sure if SQL Server could use any index to optimize query in this case.