Sunday, 24 March 2013

Creating a Calendar Table

A Calendar table is always useful so this is a script I created a long time ago to create one.
Basically I go through one day at a time and find all the variations I want for that particular day.
You just need to adjust at the bottom of the query as to what you want to do with the data once you have it.

Excuse the formatting I saved this one into a Google Document many moons ago and it played around with how it looks.

varchar(10) = 20050101

varchar(10) = 20131231


DECLARE @CalTable as TABLE (
datetime, IntDate int, [DayOfMonth] int, [DayOfYear] int, MonthOfYear int, [MonthName] varchar(20), [Year] int, CalQuarter int, WeekOfYear int, Weekday int, WeekDayName varchar(20), MondayOfWeek datetime, FortNight int

LastDayOfPreviousMonth datetime, LastDayOfCurrentMonth datetime, LastDayOfNextMonth datetime, FirstDayOfCurrentMonth datetime, FirstDayOfNextMonth datetime, FirstDayOfPreviousMonth datetime
FirstDayOfCurrentQuarter datetime, LastDayOfCurrentQuarter datetime)

--Convert from Integers to Datetime, I do wonder why I decided to start with Ints and convert to datetime but Im sure I had a good reason at the time
DECLARE @FromDateD as datetime

DECLARE @ToDateD as datetime

SET @FromDateD = CAST(CAST(@FromDate as varchar(8))as datetime)

SET @ToDateD = CAST(CAST(@ToDate as varchar(8))as datetime)

--Calculate for each day in range the set values

WHILE @FromDateD <= @ToDateD

--Insert each day into the temp table each time it goes through the loop
INTO @CalTable
@FromDateD AS [Date],

CONVERT (varchar, @FromDateD, 112) As IntDate,

DAY(@FromDateD) As [DayOfMonth],

DATEPART(dayofyear, @FromDateD) as [DayOfYear],

Month(@FromDateD) as MonthOfYear,

DATENAME(month, @FromDateD) AS [MonthName],

YEAR(@FromDateD) as [Year],

DATEPART(quarter, @FromDateD) as CalQuarter,

DATEPART(week, @FromDateD) as WeekOfYear,

DATEPART(weekday, @FromDateD) as [Weekday],

DATENAME(weekday, @FromDateD) AS [WeekDayName],

DATEADD(wk, DATEDIFF(wk,0,@FromDateD), 0) as MondayOfWeek,

CASE when DATEPART(week, @FromDateD) in (2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, 50, 52, 54, 56)
Then 2 else 1 END AS FortNight,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@FromDateD),0)) as LastDayOfPreviousMonth
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@FromDateD)+1,0)) as LastDayOfCurrentMonth,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@FromDateD)+2,0)) as LastDayOfNextMonth,
DATEADD(mm, DATEDIFF(m,0,@FromDateD),0) as FirstDayOfCurrentMonth,
DATEADD(mm, DATEDIFF(m,0,@FromDateD)+1,0) as FirstDayOfNextMonth,
DATEADD(mm, DATEDIFF(m,0,@FromDateD)-1,0) as FirstDayOfPreviousMonth,
DATEADD(qq, DATEDIFF(qq,0,@FromDateD), 0) as FirstDayOfCurrentQuarter,
DATEADD(s,-1,DATEADD(qq, DATEDIFF(q,0,@FromDateD)+1,0)) as LastDayOfCurrentQuarter

--Set the day + 1 so the loop moves on to the next day
SELECT @FromDateD = DATEADD(DD, 1, @FromDateD)

--INSERT INTO CalendarTable --If you have an existing table
--INTO CalendarTable --If you one to create a new table
@CalTable ORDER BY [Date]