CREATE TABLE [dbo].[DimTime](
[TransactionDate] [datetime] NOT NULL,
[DateText] [varchar](10) NULL,
[CalenderYear] [int] NULL,
[CalenderQuarter] [varchar](50) NULL,
[CalenderMonth] [varchar](50) NULL,
[CalenderMonthNumber] [int] NULL,
[CalenderWeek] [varchar](50) NULL,
[CalenderWeekNumber] [int] NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [varchar](50) NULL,
[FiscalPeriod] [varchar](50) NULL,
[FiscalPeriodNumber] [int] NULL,
[Day] [int] NULL,
[DayOfWeek] [tinyint] NULL,
CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED
(
[TransactionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[buildDimTime]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @date datetime
declare @EndDate datetime
--set @date = '01/01/2006'
set @date = DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-3, 0) --Only calculates last 3 years
set @EndDate = (DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 2, 0)))
delete DimTime
while @date <= @EndDate
begin
insert into dimTime
select @date as TransactionDate,convert(varchar(10),@date,111),
DatePart(year,@date) as Year,
cast(DatePart(year,@date) as varchar(4)) + ' ' + 'Q' + convert(CHAR,DatePart(quarter,@date)) as QuarterNumber,
cast(DatePart(year,@date) as varchar(4)) + ' ' + convert(CHAR,DateName(month,@date)) as MonthName,
DatePart(month,@date) as MonthNumber,
'Week' + convert(CHAR, DATEPART(wk, @date)) as CalenderWeek,
DATEPART(wk, @date) as CalenderWeekNumber,
--'Week' as WeekNumber,
--DateName(day,@date) as DayNumber,
DatePart(year,@date) as FiscalYear,
'Q' + convert(CHAR,DatePart(quarter,@date)) as FiscalQuarter,
convert(CHAR,DateName(month,@date)) as FiscalPeriod,
DatePart(month,@date) as FiscalPeriodNumber,
(select datediff(d, (select DATEADD(yy, DATEDIFF(yy,0,@date), 0)),@date)) as DayNumber,
DATEPART(dw, @date) as DayOfWeek
set @date = @date + 1
end
--update dimTime set DateText = convert(varchar(10),TransactionDate,111)
--from dimTime
END
