Generating Calendar Table

Recently I've seen couple of posts where posters asks for requirement for generating day by day reports like sales analysis report showing all day info. In such cases, we might need to generate a calendar table on the fly to show day by day report as sales table may have gaps in data missing few days. With SQL 2005 recursive CTE's its now quite easy to generate a calendar table. Given below is a table valued function which generates the calendar table for a period determined by parameters @StartDate and @EndDate. An optional parameter @WeekDaysOnly determines if we need to consider only weekdays.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='CalendarTable' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.CalendarTable
GO
CREATE FUNCTION dbo.CalendarTable
(
@StartDate datetime,
@EndDate datetime,
@WeekDaysOnly bit = 0,
@MonthStart bit=0,
@YearStart bit = 0
)
RETURNS @CALENDAR TABLE
(
Date datetime,
Day varchar(20),
WeekDay bit,
MonthStart bit,
YearStart bit
)
AS
BEGIN
;With Calendar_CTE (Date,Day,WeekDay,MonthStart,YearStart)
AS
(
SELECT @StartDate,DATENAME(dw,@StartDate), CASE WHEN DATENAME(dw,@StartDate) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,CASE WHEN DATEPART(dd,@StartDate) = 1 THEN 1 ELSE 0 END
,CASE WHEN DATEPART(dd,@StartDate) = 1 AND  DATEPART(mm,@StartDate) = 1 THEN 1 ELSE 0 END

UNION ALL

SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATENAME(dw,DATEADD(dd,1,Date)) NOT IN ('Saturday','Sunday') THEN 1 ELSE 0 END,
CASE WHEN DATEPART(dd,DATEADD(dd,1,Date)) = 1 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(dd,DATEADD(dd,1,Date)) = 1 AND DATEPART(mm,DATEADD(dd,1,Date)) = 1 THEN 1 ELSE 0 END
FROM Calendar_CTE
WHERE DATEADD(dd,1,Date) < = @EndDate
)

INSERT INTO @CALENDAR
SELECT Date,Day,WeekDay,MonthStart,YearStart
FROM Calendar_CTE
WHERE (WeekDay=1
OR @WeekDaysOnly = 0)
AND (MonthStart=1
OR @MonthStart=0)
AND (YearStart = 1 
OR @YearStart = 0)
OPTION (MAXRECURSION 0)

RETURN
END

Example usage

SELECT Date,Day,WeekDay FROM dbo.CalendarTable('2010-01-01','2010-02-28',0,0,0)