Code for populating the 'date' dimension
For context, please see last tip .
create procedure Load_Date_Dimension_Sp @year_start int, @year_end int as begin declare @yr int set @yr = year_start while (@yr <= year_end) begin insert into year_d (the_year) values (@yr) set @yr = @yr + 1 end declare @qt int set @qt = 1 while (@qt <= 4) begin insert into quarter_d (the_quarter) values (@qt) set @qt = @qt + 1 end declare @mo int set @mo = 1 while (@mo <= 12) begin insert into month_d (the_month) values (@mo) set @mo = @mo + 1 end update month_d set quarter_key = (select quarter_key from quarter_d where the_quarter=1) where the_month in (1,2,3) update month_d set quarter_key = (select quarter_key from quarter_d where the_quarter=2) where the_month in (4,5,6) update month_d set quarter_key = (select quarter_key from quarter_d where the_quarter=3) where the_month in (7,8,9) update month_d set quarter_key = (select quarter_key from quarter_d where the_quarter=4) where the_month in (10,11,12) declare @wk int set @wk = 1 while (@wk <= 52) begin insert into week_d (the_week) values (@wk) set @wk = @wk + 1 end declare @dt1 int set @dt1 = 1 while (@dt1 <= 31) begin insert into day_d (the_day) values (@dt1) set @dt1 = @dt1 + 1 end declare @dt datetime declare @dt_end = ?12/31/? + @year_end declare @day_k int declare @week_k int declare @month_k int declare @year_k int set @dt = '01/01/? + @year_start while (@dt <= @dt_end) begin select @year_k=year_key from year_d where the_year = year(@dt) select @month_k=month_key from month_d where the_month = month(@dt) select @day_k=day_key from day_d where the_day = day(@dt) select @week_k=week_key from week_d where the_week = datepart(wk,@dt) insert into date_d (the_date, day_key, week_key, month_key, year_key) values (@dt, @day_k, @week_k, @month_k, @year_k) set @dt = @dt + 1 end end