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.


DECLARE
@FromDate
varchar(10) = 20050101



DECLARE
@ToDate
varchar(10) = 20131231




SET NOCOUNT ON;


DECLARE @CalTable as TABLE (
[Date]
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

BEGIN
--Insert each day into the temp table each time it goes through the loop
INSERT
INTO @CalTable
SELECT
@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)
END

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