declare @CurrentDate date
declare @NZFiscalYearStartMonth int
declare @AUFiscalYearStartMonth int
declare @WeeklyHolidays table ([WeekDay] int) -- weekday, sunday is 1 and saturday is 7
declare @NZAnnulPublicHolidays table([Date] int,[Description] varchar(50)) -- int in YYYYMMDD format
declare @AUAnnulPublicHolidays table([Date] int,[Description] varchar(50)) -- int in YYYYMMDD format
declare @FirstDate date
declare @NumberOfYearsToGenerate int
declare @LastDate date
declare @NZFiscalStartDateForCurrentYear date
declare @AUFiscalStartDateForCurrentYear date

set @FirstDate='1990-01-01'
set @NumberOfYearsToGenerate=40
-- do not change line below
set @LastDate=DATEADD(YEAR,@NumberOfYearsToGenerate,@FirstDate)
set @CurrentDate=@FirstDate
set @NZFiscalYearStartMonth=4
set @AUFiscalYearStartMonth=7

-- insert weekly holidays
insert into @WeeklyHolidays([WeekDay]) values(1) -- sunday
insert into @WeeklyHolidays([WeekDay]) values(7) -- saturday
-- insert annual public holidays
-- 2013 NZ holidays
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130101,'New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130102,'Day after New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130206,'Waitangi Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130329,'Good Friday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130401,'Easter Monday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130425,'ANZAC Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20130603,'Queen''s Birthday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131028,'Labour Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131225,'Christmas Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20131226,'Boxing Day')
-- 2014 NZ holidays
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140101,'New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140102,'Day after New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140206,'Waitangi Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140418,'Good Friday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140421,'Easter Monday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140425,'ANZAC Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20140602,'Queen''s Birthday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141027,'Labour Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141225,'Christmas Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20141226,'Boxing Day')
-- 2015 NZ holidays
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150101,'New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150102,'Day after New Year''s Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150206,'Waitangi Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150403,'Good Friday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150406,'Easter Monday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150427,'ANZAC Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20150601,'Queen''s Birthday')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151026,'Labour Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151225,'Christmas Day')
insert into @NZAnnulPublicHolidays([Date],[Description]) values(20151228,'Boxing Day')
-- 2013 AU ACT holidays
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130101,'New Year''s Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130128,'Australia Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130311,'Canberra Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130329,'Good Friday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130330,'Easter Saturday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130401,'Easter Monday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130425,'ANZAC Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130610,'Queen''s Birthday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20130930,'Family & Community Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131007,'Labour Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131225,'Christmas Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20131226,'Boxing Day')
-- 2014 AU ACT holidays
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140101,'New Year''s Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140127,'Australia Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140310,'Canberra Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140418,'Good Friday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140419,'Easter Saturday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140421,'Easter Monday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140425,'ANZAC Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140609,'Queen''s Birthday')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20140929,'Family & Community Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141006,'Labour Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141225,'Christmas Day')
insert into @AUAnnulPublicHolidays([Date],[Description]) values(20141226,'Boxing Day')
while(@CurrentDate<@LastDate)
begin
set @NZFiscalStartDateForCurrentYear=
case when datediff(day,convert(date,datename(year,@CurrentDate)+'-'+convert(varchar(2),@NZFiscalYearStartMonth)+'-01'),@CurrentDate)>=0 then
convert(date,datename(year,@CurrentDate)+'-'+convert(varchar(2),@NZFiscalYearStartMonth)+'-01')
else
convert(date,datename(year,dateadd(year,-1,@CurrentDate))+'-'+convert(varchar(2),@NZFiscalYearStartMonth)+'-01')
end

set @AUFiscalStartDateForCurrentYear=
case when datediff(day,convert(date,datename(year,@CurrentDate)+'-'+convert(varchar(2),@AUFiscalYearStartMonth)+'-01'),@CurrentDate)>=0 then
convert(date,datename(year,@CurrentDate)+'-'+convert(varchar(2),@AUFiscalYearStartMonth)+'-01')
else
convert(date,datename(year,dateadd(year,-1,@CurrentDate))+'-'+convert(varchar(2),@AUFiscalYearStartMonth)+'-01')
end

INSERT INTO [dbo].[DimDate]
           ([DateKey]
           ,[DateFullName]
           ,[FullDateAlternateKey]
           ,[YearCode]
           ,[YearDescription]
           ,[QuarterCode]
           ,[QuarterDescription]
           ,[QuarterYearCode]
           ,[MonthNumberOfYearCode]
           ,[MonthYearCode]
           ,[MonthName]
           ,[MonthYearDescription]
           ,[MonthLocalizedString]
           ,[DateLocalizedString]
           ,[DayNumberOfMonth]
           ,[NumberOfDaysInTheMonth]
           ,[DayNumberOfYear]
           ,[WeekNumberOfYear]
           ,[WeekYearCode]
           ,[ISOWeekNumberOfYear]
           ,[ISOWeekYearCode]
           ,[WeekDay]
           ,[WeekDayName]
           ,[NZFiscalYearCode]
           ,[NZFiscalYearDescription]
           ,[NZFiscalQuarterCode]
           ,[NZFiscalQuarterYearCode]
           ,[NZFiscalQuarterDescription]
           ,[NZFiscalMonthCode]
           ,[NZFiscalMonthYearCode]
           ,[NZFiscalMonthDescription]
           ,[AUFiscalYearCode]
           ,[AUFiscalYearDescription]
           ,[AUFiscalQuarterCode]
           ,[AUFiscalQuarterYearCode]
           ,[AUFiscalQuarterDescription]
           ,[AUFiscalMonthCode]
           ,[AUFiscalMonthYearCode]
           ,[AUFiscalMonthDescription]
           ,[IsWeekDayCode]
           ,[IsWeekDayDescription]
           ,[IsNZPublicHolidayCode]
           ,[IsNZPublicHolidayDescription]
           ,[NZPublicHolidayFullDescription]
           ,[IsAUPublicHolidayCode]
           ,[IsAUPublicHolidayDescription]
           ,[AUPublicHolidayFullDescription]
           ,[CurrentYear]
           ,[CurrentMonth]
           ,[CurrentDay])

select
convert(int,convert(varchar(8),@CurrentDate,112)) as [DateKey],
convert(varchar(max),@CurrentDate,106) as [DateFullName],
@CurrentDate as [FullDateAlternateKey],
datepart(year,@CurrentDate) as [YearCode],
'CY '+datename(year,@CurrentDate) as [YearDescription],
datepart(QUARTER,@CurrentDate) as [QuarterCode],
'QTR '+datename(QUARTER,@CurrentDate) as [QuarterDescription],
convert(int,datename(year,@CurrentDate)+datename(QUARTER,@CurrentDate)) as [QuarterYearCode],
datepart(month,@CurrentDate) as [MonthNumberOfYearCode],
convert(int,datename(year,@CurrentDate)+right('0'+convert(varchar(2),datepart(month,@CurrentDate)),2)) as [MonthYearcode],
datename(month,@CurrentDate) as [MonthName],
datename(month,@CurrentDate)+' '+datename(year,@CurrentDate) as [MonthYearDescription],
substring(
	convert(varchar(max),@CurrentDate,103),
	charindex('/',convert(varchar(max),@CurrentDate,103),1)+1,
	len(convert(varchar(max),@CurrentDate,103))-charindex('/',convert(varchar(max),@CurrentDate,103),1)
	) as [MonthLocalizedString],
convert(varchar(max),@CurrentDate,103) as [DateLocalizedString],
datepart(day,@CurrentDate) as [DayNumberOfMonth],
datepart(day,EOMONTH(@CurrentDate)) as [NumberOfDaysInTheMonth],
datepart(DAYOFYEAR,@CurrentDate) as [DayNumberOfYear],
datepart(WEEK,@CurrentDate) as [WeekNumberOfYear],
datename(year,@CurrentDate)+right('0'+datename(week,@CurrentDate),2) as [WeekYearCode],
datepart(ISO_WEEK,@CurrentDate) as [ISOWeekNumberOfYear],
datename(year,@CurrentDate)+right('0'+convert(varchar(2),datepart(ISO_WEEK,@CurrentDate)),2) as [ISOWeekYearCode],
datepart(WEEKDAY,@CurrentDate) as [WeekDay],
datename(WEEKDAY,@CurrentDate) as [WeekDayName],
year(@NZFiscalStartDateForCurrentYear)+1 as [NZFiscalYearCode],
'FY '+convert(varchar(4),year(@NZFiscalStartDateForCurrentYear)+1) as [NZFiscalYearDescription],
datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1 as [NZFiscalQuarterCode],
((datepart(year,@NZFiscalStartDateForCurrentYear)+1)*10)+(datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalQuarterYearCode],
'QTR '+convert(varchar(1),datediff(QUARTER,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalQuarterDescription],
datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1 as [NZFiscalMonthCode],
((datepart(year,@NZFiscalStartDateForCurrentYear)+1)*100)+(datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalMonthYearCode],
'Month '+convert(varchar(2),datediff(month,@NZFiscalStartDateForCurrentYear,@CurrentDate)+1) as [NZFiscalMonthDescription],
year(@AUFiscalStartDateForCurrentYear)+1 as [AUFiscalYearCode],
'FY '+convert(varchar(4),year(@AUFiscalStartDateForCurrentYear)+1) as [AUFiscalYearDescription],
datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1 as [AUFiscalQuarterCode],
((datepart(year,@AUFiscalStartDateForCurrentYear)+1)*10)+(datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalQuarterYearCode],
'QTR '+convert(varchar(1),datediff(QUARTER,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalQuarterDescription],
datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1 as [AUFiscalMonthCode],
((datepart(year,@AUFiscalStartDateForCurrentYear)+1)*100)+(datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalMonthYearCode],
'Month '+convert(varchar(2),datediff(month,@AUFiscalStartDateForCurrentYear,@CurrentDate)+1) as [AUFiscalMonthDescription],


case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 0 else 1 end as [IsWeekDayCode],
case when datepart(WEEKDAY,@CurrentDate) in (select [weekday] from @WeeklyHolidays) then 'Weekend' else 'Weekday' end as [IsWeekDayDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then 1 else 0 end as [NZIsPublicHolidayCode],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then 'Holiday' else 'Non Holiday' end as [IsNZPublicHolidayDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @NZAnnulPublicHolidays) then (select [Description] from @NZAnnulPublicHolidays where [Date]=convert(int,convert(varchar(8),@CurrentDate,112))) else 'N/A' end as [NZPublicHolidayFullDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then 1 else 0 end as [AUIsPublicHolidayCode],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then 'Holiday' else 'Non Holiday' end as [IsAUPublicHolidayDescription],
case when convert(int,convert(varchar(8),@CurrentDate,112)) in (select [Date] from @AUAnnulPublicHolidays) then (select [Description] from @AUAnnulPublicHolidays where [Date]=convert(int,convert(varchar(8),@CurrentDate,112))) else 'N/A' end as [AUPublicHolidayFullDescription],
DATEDIFF(Year,getdate(),@CurrentDate) as [CurrentYear],
DATEDIFF(Month,getdate(),@CurrentDate) as [CurrentMonth],
DATEDIFF(Day,getdate(),@CurrentDate) as [CurrentDay]

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