Alas, I continue to find loops and cursors a challenge so I thought I'd try it out with the basics... the syntax below can be used to create a date dimension on any server; it does not reference any database tables.
NOTE: Additional columns could be added by using numerous other SQL datepart (etc.) functions.
/******************************************************
* FOR DEVELOPERS who have the rights to build a table
*******************************************************
* For testing purposes, I like to create my own schema;
* the syntax below could be done as a temp table as well
* (scroll towards the bottom of this SQL).
*******************************************************/
/* SETUP: I like to create my own schema for testing...
*
* create schema epriddyTEST
*
* --drop table epriddyTEST.dimDate
* -- create table epriddyTEST.dimDate
* -- (FullDate datetime,
* -- Year int,
* -- DayOfWeek varchar(20))
* -- create clustered index FullDateIndex on epriddyTEST.dimDate(FullDate)
********************************************************/
--I like to make the calculations relative to today
declare @Today datetime
set @Today = convert(datetime,convert(char(10),getdate(),101))
---------------------------------------
--STEP1: set up the date range desired
---------------------------------------
declare @StartDate datetime
set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today
set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st
declare @EndDate datetime
set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today
set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st
--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...
--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate
--select datediff(d,@StartDate,@EndDate) -- number of loops
---------------------------------------
--STEP2: Setup Loop piece
---------------------------------------
declare @count int; --NumberOfLoops
set @count = -1
while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop
begin
set @count = @count + 1
insert into epriddyTEST.dimDate (FullDate, [Year],[DayOfWeek])
Select FullDate = @StartDate+@count
,[Year] = datepart(yy,(@StartDate+@count ))
,[DayOfWeek]=datename(weekday,(@StartDate+@count ))
end
Select * from epriddyTEST.dimDate
--All done!
/******************************************************
* FOR ANALYSTS and Others who may not have permissions
* to create permanent tables; here's a temp table option
*******************************************************/
--I like to make the calculations relative to today
declare @Today datetime
set @Today = convert(datetime,convert(char(10),getdate(),101))
---------------------------------------
--STEP1: set up the date range desired
---------------------------------------
declare @StartDate datetime
set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today
set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st
declare @EndDate datetime
set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today
set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st
--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...
--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate
--select datediff(d,@StartDate,@EndDate) -- number of loops
---------------------------------------
--STEP2: Setup Loop piece
---------------------------------------
declare @count int; --NumberOfLoops
set @count = -1
IF (object_id ('tempdb.dbo.#dimDate') is not null)
BEGIN
DROP TABLE #dimDate
END
create table #dimDate
(FullDate datetime,
[Year] int,
[DayOfWeek] varchar(20),
MonthNum int,
MonthName varchar(20))
create clustered index FullDateIndex on #dimDate(FullDate) -- I always create a index if I have to use a temp table
while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop
begin
set @count = @count + 1
insert into #dimDate (FullDate, [Year],[DayOfWeek],MonthNum, MonthName)
Select FullDate = @StartDate+@count
,[Year] = datepart(yy,(@StartDate+@count ))
,[DayOfWeek]=datename(weekday,(@StartDate+@count ))
,MonthNum = datepart(mm,(@StartDate+@count ))
,[MonthName] = DATENAME(month, (@StartDate+@count ))
end
Select * from #dimDate
--All Done!
