Creating and Populating a Date Dimension

declare @startDateID int = 20130101
declare @endDateID int =20131231 -- 20141231
declare @truncate char(1) = 'Y'
declare @currDateID int = @startDateID
declare @currDate datetime

select @currDate = Convert(DATETIME, LEFT(@startDateID, 8))

if(@truncate = 'Y')
begin
truncate table dim_date
end

while(@currDateID <= @endDateID)
begin

select @currDate = Convert(DATETIME, LEFT(@currDateID, 8))
--print convert(varchar,@currDate)

insert into dim_date(dateID, fullDate)
values (@currDateID, @currDate)

set @currDate = DATEADD(d,1,@currDate)
set @currDateID = CONVERT(varchar,@currDate,112)


end

--
-- you may have to do something special here, or you can mark all days as tradeable
--
update dim_date set isTradeDay = 'Y'
where dateID in (select closeDateID
from stock_history
group by closeDateID
having COUNT(*) > 1000)






select @currDate = Convert(DATETIME, LEFT(@startDateID, 8))
set @currDateID = @startDateID

declare @priorDateID int
declare @prior5DateID int
declare @prior10DateID int
declare @prior30DateID int
declare @prior60DateID int
declare @prior90DateID int
declare @prior180DateID int
declare @futureDateID int
declare @future5DateID int
declare @future10DateID int
declare @future30DateID int
declare @future60DateID int
declare @future90DateID int
declare @future180DateID int

while(@currDateID <= @endDateID)
begin

select @currDate = Convert(DATETIME, LEFT(@currDateID, 8))
print convert(varchar,@currDate)

select @priorDateID = MAX(dateID) from dim_date where dateID < @currDateID and isTradeDay = 'Y'
select @prior5DateID = Min(dateID) from (select top 5 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a
select @prior10DateID = Min(dateID) from (select top 10 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a
select @prior30DateID = Min(dateID) from (select top 30 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a
select @prior60DateID = Min(dateID) from (select top 60 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a
select @prior90DateID = Min(dateID) from (select top 90 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a
select @prior180DateID = Min(dateID) from (select top 180 dateID from dim_date where dateID < @currDateID and isTradeDay = 'Y' order by dateID desc) as a

select @futureDateID = MIN(dateID) from dim_date where dateID > @currDateID and isTradeDay = 'Y'
select @future5DateID = Max(dateID) from (select top 5 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a
select @future10DateID = Max(dateID) from (select top 10 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a
select @future30DateID = Max(dateID) from (select top 30 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a
select @future60DateID = Max(dateID) from (select top 60 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a
select @future90DateID = Max(dateID) from (select top 90 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a
select @future180DateID = Max(dateID) from (select top 180 dateID from dim_date where dateID > @currDateID and isTradeDay = 'Y' order by dateID ) as a

--select Max(dateID) from (select top 5 dateID from dim_date where dateID > 20130115 and isTradeDay = 'Y' order by dateID ) as a
--select Max(dateID) from (select top 10 dateID from dim_date where dateID > 20130115 and isTradeDay = 'Y' order by dateID ) as a


update dim_date
set priorDateID = @priorDateID
,prior5DateID = @prior5DateID
,prior10DateID = @prior10DateID
,prior30DateID = @prior30DateID
,prior60DateID = @prior60DateID
,prior90DateID = @prior90DateID
,prior180DateID = @prior180DateID

,futureDateID = @futureDateID
,future5DateID = @future5DateID
,future10DateID = @future10DateID
,future30DateID = @future30DateID
,future60DateID = @future60DateID
,future90DateID = @future90DateID
,future180DateID = @future180DateID

where dateID = @currDateID

set @currDate = DATEADD(d,1,@currDate)
set @currDateID = CONVERT(varchar,@currDate,112)


end




select * from dim_date where dateID between 20130210 and 20130230