T-SQL: Calendar Table
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