declare @CurrentDate date
declare @FiscalYearStartMonth int
declare @WeeklyHolidays table ([WeekDay] int) -- weekday, sunday is 1 and saturday is 7
declare @AnnulPublicHolidays table([Date] int) -- int in YYYYMMDD format
declare @FirstDate date 
declare @NumberOfYearsToGenerate int
declare @LastDate date


set @FirstDate='1990-01-01'
set @NumberOfYearsToGenerate=40

-- do not change line below
set @LastDate=DATEADD(YEAR,@NumberOfYearsToGenerate,@FirstDate)

set @CurrentDate=@FirstDate
set @FiscalYearStartMonth=7

-- insert weekly holidays 
insert into @WeeklyHolidays([WeekDay]) values(1) -- sunday
insert into @WeeklyHolidays([WeekDay]) values(7) -- saturday

-- insert annual public holidays
insert into @AnnulPublicHolidays([Date]) values(20130101) -- New Year's Day
insert into @AnnulPublicHolidays([Date]) values(20130102) -- Day after New Year's Day
insert into @AnnulPublicHolidays([Date]) values(20130206) -- Waitangi Day 
insert into @AnnulPublicHolidays([Date]) values(20130329) -- Good Friday 
insert into @AnnulPublicHolidays([Date]) values(20130401) -- Easter Monday 
insert into @AnnulPublicHolidays([Date]) values(20130425) -- ANZAC Day 
insert into @AnnulPublicHolidays([Date]) values(20130603) -- Queen's Birthday 
insert into @AnnulPublicHolidays([Date]) values(20131028) -- Labour Day 
insert into @AnnulPublicHolidays([Date]) values(20131225) -- Christmas Day 
insert into @AnnulPublicHolidays([Date]) values(20131226) -- Boxing Day 


while(@CurrentDate<@LastDate)
begin

INSERT INTO [dbo].[DimDate]
           ([DateKey]
           ,[DateFullName]
           ,[DateFull]
           ,[Year]
           ,[Quarter]
           ,[QuarterName]
           ,[QuarterKey]
           ,[Month]
           ,[MonthKey]
           ,[MonthName]
           ,[DayOfMonth]
           ,[NumberOfDaysInTheMonth]
           ,[DayOfYear]
           ,[WeekOfYear]
           ,[WeekOfYearKey]
           ,[ISOWeek]
           ,[ISOWeekKey]
           ,[WeekDay]
           ,[WeekDayName]
           ,[FiscalYear]
           ,[FiscalQuarter]
           ,[FiscalQuarterKey]
           ,[FiscalMonth]
           ,[FiscalMonthKey]
           ,[IsWorkDayKey]
           ,[IsWorkDayDescription]
           ,[IsPublicHolidayKey]
           ,[IsPublicHolidayDescription])


select 
convert(int,convert(varchar(8),@CurrentDate,112)) as [DateKey],
convert(varchar(max),@CurrentDate,106) as [DateFullName],
@CurrentDate as [DateFull],
datepart(year,@CurrentDate) as [Year],
datepart(QUARTER,@CurrentDate) as [Quarter],
'QTR '+datename(QUARTER,@CurrentDate) as [QuarterName],
convert(int,datename(year,@CurrentDate)+datename(QUARTER,@CurrentDate)) as [QuarterKey],
datepart(month,@CurrentDate) as [Month],
convert(int,datename(year,@CurrentDate)+right('0'+convert(varchar(2),datepart(month,@CurrentDate)),2)) as [MonthKey],
datename(month,@CurrentDate) as [MonthName],
datepart(day,@CurrentDate) as [DayOfMonth],
datepart(day,EOMONTH(@CurrentDate)) as [NumberOfDaysInTheMonth],
datepart(DAYOFYEAR,@CurrentDate) as [DayOfYear],
datepart(WEEK,@CurrentDate) as [WeekOfYear],
datename(year,@CurrentDate)+right('0'+datename(week,@CurrentDate),2) as [WeekOfYearKey],
datepart(ISO_WEEK,@CurrentDate) as [ISOWeek],
datename(year,@CurrentDate)+right('0'+convert(varchar(2),datepart(ISO_WEEK,@CurrentDate)),2) as [ISOWeekKey],
datepart(WEEKDAY,@CurrentDate) as [WeekDay],
datename(WEEKDAY,@CurrentDate) as [WeekDayName],
case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end as [FiscalYear],
ceiling(convert(float,(case when month(@CurrentDate)=13-@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth-1)+month(@CurrentDate))%12 end))/3) as [FiscalQuarter],
convert(varchar(4),case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end)
+
convert(varchar(1),ceiling(convert(float,(case when month(@CurrentDate)=13-@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth-1)+month(@CurrentDate))%12 end))/3))
as [FiscalQuarterKey],
case when month(@CurrentDate)=13-@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth-1)+month(@CurrentDate))%12 end as [FiscalMonth],
convert(varchar(4),case when month(@CurrentDate)<@FiscalYearStartMonth then year(@CurrentDate) else year(@CurrentDate)+1 end)
+
right('0'+convert(varchar(2),case when month(@CurrentDate)=13-@FiscalYearStartMonth then 12 else ((@FiscalYearStartMonth-1)+month(@CurrentDate))%12 end),2)
as [FiscalMonthKey],
case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 1 else 0 end as [IsWorkDayKey],
case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 'Weekend' else 'Workday' end as [IsWorkDayDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AnnulPublicHolidays) then 1 else 0 end as [IsPublicHolidayKey],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AnnulPublicHolidays) then 'Holiday' else 'Non Holiday' end as [IsPublicHolidayDescription]


set @CurrentDate=dateadd(day,1,@CurrentDate)
end