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 days,leave days,Holidays, 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;

How to design a Calender table in Sql Server 2008 R2

Answers (1)