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)

Published by dataplatformcentral

2 thoughts on “Generating Calendar Table”

  1. Hello, I know the thread is old but I have couple of remarks.
    1/- I changed the script with the following so that it does not count non worked days(holidays) with the following code:
    where c.Date not in (select Holidaydate from [TRIAD-SQL-02DEVL2].Reports.dbo.traidholiday)
    which as been added after the last from statement.
    2/-I have tested your script without the modification above criteria with end and start date being the same and altered it to show results in seconds, by altering the last DATEDIFF function to second and by removing the final /60.0. In this scenario, I get 0. see following query: SELECT dbo.BusinessHours('2012-03-15','2012-03-15','08:30','17:15')
    When running the query below, I get just 1:
    SELECT dbo.BusinessHours('2012-03-15 08:44:44','2012-03-15 10:12:34.533','08:30','17:15')
    Dou have anythoughts as of why it would not return actual seconds elapsed within that date range?
    Thanks for helping with this.

  2. I think you've commented on wrong blog. Anyways here are answers

    1. I've Holiday table which I'm using in last join to identify holidays. just need to add it to first part as well. Will edit it to include this condition too. Thanks for the feedback

    2. reason why it doesnt work as desired is you're not sending period in correct format. sending just date will default it to 12 midnight (start of day). try this way and see

    SELECT dbo.BusinessHours('2012-03-15 00:00','2012-03-15 23:59','08:30','17:15')

    and if you dont want to pass time you need pass open interval like below

    SELECT dbo.BusinessHours('2012-03-15','2012-03-16','08:30','17:15')

Comments are closed.