Friday, August 30, 2013

Calender table in sql server

Create a generic calender table and make use of it to calculate the Date ranges/ Weekdays/Weekends/Quarters in a year/day of a week/Month of date...

Below is the script to generate a calender.


IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Generic_Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Generic_Calendar]
END

CREATE TABLE [Generic_Calendar]
(
DSNo INT Identity(1,1) primary Key,
    [CalendarDate]  DATE,
    WeekDay_End Tinyint,
    DayDate Tinyint,
    DayOfWeekName   Varchar(20),
    WeekNumber Tinyint,
    MonthNumber Tinyint,
    [MonthName] varchar(20),
    YearOftheDate SmallInt,
    QuarterNumber Tinyint
 )
 
 
go

 declare @date int
 WITH CTE_DatesTable
AS
(
  SELECT CAST('20000101' as date) AS [date]
  UNION ALL
  SELECT   DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '21001231'
)
insert into [Generic_Calendar] ([CalendarDate],DayDate,WeekDay_End,DayOfWeekName,WeekNumber,MonthNumber,[MonthName],YearOftheDate,QuarterNumber )
SELECT [DWDateKey]=[date],[DayDate]=datepart(dd,[date]),DATEPART(weekday,[date]),
[DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH ,
[date]),[MonthName]=DATENAME( MONTH , [date]),[Year]=DATEPART(YY,[date]),
[QuarterNumber]=DATENAME(quarter, [date])
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
go

To get the start and end dates of 1 quarter in a year,
   Ex:     select MIN(CalendarDate),MAX(CalendarDate) from [Generic_Calendar] where                                                YearOftheDate='2013' and QuarterNumber=1  

This way , one can easily do calculations based on dates.

No comments: