In this article I would like to shed some light on the Calendar Table Object.

Introduction

I want to discuss the concept of Calendar Table because I have seen many times developers having trouble with dates, weekends, holidays, weekdays etc.
I advise that in a project the Calendar Table should always be incorporated. Usually I term this table as "DimDate".
I am assuming that here we all know the concept of Dimensions and Fact Tables in a DWH.

Object Creation Script

DimDate_ForumArticle
IF OBJECT_ID('DimDate_ForumArticle') IS NOT NULL
       DROP TABLE DBO.DimDate_ForumArticle


CREATE TABLE [dbo].[DimDate_ForumArticle](
       [Date_key] [int] PRIMARY KEY NOT NULL ,
       [Date] [date] NOT NULL,
       [Day] [int] NOT NULL,
       [DayNumber_of_Month] [int] NOT NULL,
       [Day_Name] [nvarchar](30) NOT NULL,
       [Week_Of_Month] [varchar](20) NOT NULL,
       [Week_Number] [int] NOT NULL,
       [Month_Number] [int] NOT NULL,
       [Month_Name] [nvarchar](30) NOT NULL,
       [Quarter_Number] [int] NOT NULL,
       [Semester] [varchar](1) NOT NULL,
       [Year] [int]NOT NULL,
       [Fiscal_Month] [int]NOT NULL,
       [Fiscal_Quarter] [int]NOT NULL,
       [Fiscal_Semester] [varchar](1) NOT NULL,
       [Fiscal_Year] [int]NOT NULL,
       [isweekEND] [varchar](1) NOT NULL,
       [isHoliday] [int] NOT NULL)
ON PRIMARY
GO

ListOfHolidays_ForumArticle
IF OBJECT_ID('ListOfHolidays_ForumArticle') IS NOT NULL
       DROP TABLE DBO.ListOfHolidays_ForumArticle


CREATE TABLE [dbo].ListOfHolidays_ForumArticle(
       [HolidayID] INT IDENTITY PRIMARY KEY,
       [DateOfHoliday] [date] NOT NULL)

Procedure Script

CREATE PROCEDURE [dbo].[uspPopulateDimDate_ForumArticle]
@start_date date,
@end_date date
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE DimDate_ForumArticle
DECLARE @monthoffset INT,
@first_fiscal_date DATE
 
SET @monthoffset=3
 
WHILE (@start_date<=@end_date)
BEGIN
SET @first_fiscal_date = DATEADD(MONTH,-1*@monthoffset, @start_date)
 
INSERT INTO DimDate_ForumArticle(
[Date_key],[Date],[Day],[DayNumber_of_Month],[Day_Name],[Week_Of_Month],[Week_Number],[Month_Number],[Month_Name],[Quarter_Number],[Semester],
[Year],[Fiscal_Month],[Fiscal_Quarter],[Fiscal_Semester],[Fiscal_Year],[isWeekEND],[isHoliday])
 
SELECT
[Date_key]=CONVERT(int,CONVERT(VARCHAR(20), @start_date, 112)),
[Date]= @start_date,
[Day]= DATEPART(WEEKDAY, @start_date),
[DayNumber_of_Month]= DATEPART(day, @start_date),
[Day_Name]= DATENAME(WEEKDAY, @start_date),
[Week_Of_Month]= +CONVERT(VARCHAR(20),DATEPART(WEEK, @start_date) - DATEPART(WEEK, CONVERT(CHAR(6), @start_date, 112) + '01') + 1),
 
[Week_Number]= CASE  WHEN DATEPART(day, @start_date) between 1 and 7 THEN 1
           WHEN DATEPART(DAY, @start_date) between 8 and 14 THEN 2
           WHEN DATEPART(DAY, @start_date) between 15 and 21 THEN 3
           WHEN DATEPART(DAY, @start_date) between 22 and 28 THEN 4
           WHEN DATEPART(DAY, @start_date) >28 THEN 5
 END,
 
[Month_Number] = DATEPART(MONTH, @start_date),
[Month_Name] = DATENAME(MONTH, @start_date),
[Quarter_Number] = DATEPART(QUARTER, @start_date),
 
[Semester] = CASE WHEN DATEPART(MONTH, @start_date) <=6 THEN '1'
 ELSE '2'
END,
 
[Year] = DATEPART(YEAR,@start_date),
[Fiscal_Month] = DATEPART(MONTH, @first_fiscal_date),
[Fiscal_Quarter] = DATEPART(QUARTER, @first_fiscal_date),
 
[Fiscal_Semester] = CASE WHEN DATEPART(MONTH, @first_fiscal_date) <7 THEN '1'
            ELSE '2'
 END,
 
Fiscal_Year = DATEPART(YEAR,@first_fiscal_date)+1,
isweekEND = CASE WHEN DATENAME(WEEKDAY, @start_date) in ('Saturday', 'Sunday') THEN '1'
   ELSE '0'
END,
 
isHoliday = CASE WHEN @start_date IN (SELECT LOH.DateOfHoliday FROM ListOfHolidays_ForumArticle LOH ) THEN 1
ELSE 0
END
 
SET @start_date =DATEADD(dd, 1, @start_date)
END
END



Now we will load this table with ten years of data.

EXEC [uspPopulateDimDate_ForumArticle]'20100101','20201231'


*Note: Table object "ListOfHolidays_ForumArticle" is used in case we have a set of dates which are announced as holidays in an organization.
Insert the set of Dates in this object and we are good to go.

Usage

Now since we have our dimension table (Calendar Object), we shall use it for easing our pain.

Problem Statement 1: Number of weekends between two dates

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate='2012-01-01'
SET @EndDate='2012-03-26'

SELECT COUNT(1) AS NumOfWeekEnds
FROM DimDate_ForumArticle
WHERE Date>=@StartDate AND Date<=@EndDate AND isweekEND=1

Problem Statement 2: Difference between two dates excluding weekends


DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate='2012-01-01'
SET @EndDate='2012-03-26'
 
SELECT COUNT(1) AS DaysWOWeekends
FROM  DimDate_ForumArticle
WHERE Date>=@StartDate AND Date<=@EndDate AND isweekEND=0

Problem Statement 3: Displaying if it's a working day


INSERT ListOfHolidays_ForumArticle
SELECT '2014-01-26' --INDIAN REPUBLIC DAY :)
 
EXEC [uspPopulateDimDate_ForumArticle]'2010/01/01','2020/12/31'
 
DECLARE @ExecutionDate DATE
SET @ExecutionDate ='2014-01-26'
 
SELECT SYST.*
FROM SYSOBJECTS SYST LEFT JOIN DimDate_ForumArticle DD ON DD.Date=@ExecutionDate
WHERE isHoliday=0

Problem Statement 4: Number of Fridays between two dates


DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate='2012-01-01'
SET @EndDate='2012-03-26'
 
SELECT COUNT(1) AS NumOfFriday
FROM DM_CRM..DimDate
WHERE Day=6     --OR Day_Name='Friday'
AND Date>=@StartDate AND Date<=@EndDate

Conclusion

What we have seen is a table object which contains dates and its related attributes. There are many problems we face in real time projects; The Problem Statements stated here are just for illustration purposes. In actual projects, this object is extremely powerful and has the capability of saving you precious time.


See Also