How to design a Calender table in Sql Server 2008 R2

Question

How to design a Calender table in Sql Server 2008 R2?. Like if we have calender table than i can store the working daysleave daysHolidays weekends. Is that possible to design all these in one table?. Or do i need a master and transaction tables?

Khan Abrar Ahmed · Answer

Hi create one table and update the table as your holidays for example table below script: --To create table SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DIM_Date]( [DateKey] [int] NOT NULL [DateValue] [date] NULL [FullDate_DDMMYYYY] [char](10) NULL [FullDate_MMDDYYYY] [char](10) NULL [FullDate_DDMMMYYYY] [char](11) NULL [DayOfMonth] [char](10) NULL [DayOfWeekFull] [char](10) NULL [DayOfWeekShort] [char](10) NULL [Month] [char](10) NULL [MonthNameFull] [char](10) NULL [MonthNameShort] [char](10) NULL [Quarter] [char](1) NULL [QuarterName] [char](10) NULL [Year] [char](4) NULL [YearName] [char](7) NULL [MonthYear] [char](10) NULL [FirstDayOfMonth] [date] NULL [LastDayOfMonth] [date] NULL [FirstDayOfQuarter] [date] NULL [LastDayOfQuarter] [date] NULL [FirstDayOfYear] [date] NULL [LastDayOfYear] [date] NULL [IsWeekday] [bit] NULL [IsHoliday] [bit] NULL CONSTRAINT [PK_Dim_Date] PRIMARY KEY CLUSTERED ( [DateKey] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO --and use the below procdeure to populate the dates IF((SELECT COUNT(1) FROM sysobjects WHERE name='p_PopulateDateDimensionTable' )>0) BEGIN DROP PROCEDURE p_PopulateDateDimensionTable END Go /* SELECT * FROM [dbo].[Dim_Date] DELETE FROM [dbo].[Dim_Date] EXECUTE [dbo].[p_PopulateDateDimensionTable] '2004-01-01' '2018-12-31' */ create PROCEDURE [dbo].[p_PopulateDateDimensionTable] ( @StartDate DATE @EndDate DATE ) AS BEGIN SET NOCOUNT ON; DECLARE @Date DATE; SET @Date = @StartDate; WHILE (@Date BEGIN IF ( SELECT COUNT(*) FROM DIM_Date WHERE DateValue = @Date ) = 0 BEGIN INSERT INTO [dbo].[DIM_Date] ( [DateKey] [DateValue] [FullDate_DDMMYYYY] [FullDate_MMDDYYYY] [FullDate_DDMMMYYYY] [DayOfMonth] [DayOfWeekFull] [DayOfWeekShort] [Month] [MonthNameFull] [MonthNameShort] [Quarter] [QuarterName] [Year] [YearName] [MonthYear] [FirstDayOfMonth] [LastDayOfMonth] [FirstDayOfQuarter] [LastDayOfQuarter] [FirstDayOfYear] [LastDayOfYear] [IsWeekday] [IsHoliday] ) SELECT [DateKey] [Date] [FullDate_DDMMYYYY] [FullDate_MMDDYYYY] [FullDate_DDMMMYYYY] [DayOfMonth] [DayOfWeekFull] [DayOfWeekShort] [Month] [MonthNameFull] [MonthNameShort] [Quarter] [QuarterName] [Year] [YearName] [MonthYear] [FirstDayOfMonth] [LastDayOfMonth] [FirstDayOfQuarter] [LastDayOfQuarter] [FirstDayOfYear] [LastDayOfYear] [IsWeekday] 0 AS [IsHoliday] FROM [dbo].[fn_GetDateDimensionAttributes](@Date); SET @Date = DATEADD(DAY 1 @Date); END; ELSE BEGIN SET @Date = DATEADD(DAY 1 @Date); END; END; SET NOCOUNT OFF; END;