Step #1. Import days.txt into dim_day.date_key

update dbo.dim_day
set date_type = 'Normal' 

update dbo.dim_day
set full_date = dateadd(Day, date_key-1, cast('1900-01-01' as datetime))

delete dbo.dim_day
where full_date >= cast('2500-01-01' as datetime)

update dbo.dim_day
set day_num_of_week = datepart(weekday, full_date) 

update dbo.dim_day
set day_num_of_month = day(full_date)

update dbo.dim_day
set day_of_week_name = upper(datename(dw, full_date)) 
from dbo.dim_day

update dbo.dim_day
set day_of_week_abbreviation = left(day_of_week_name,3)

update dbo.dim_day
set day_num_of_year = datename(dayofyear, full_date)

update dbo.dim_day
set day_num_absolute = date_key

update dbo.dim_day
set year_num = year(full_date)

update dbo.dim_day
set month_name = upper(datename(month, full_date))
, month_name_abbreviation = left(upper(datename(month,full_date)),3)

update dbo.dim_day
set is_weekday = case when day_num_of_week in (1,7) then 'N' else 'Y' end

update dbo.dim_day
set is_last_day_of_week = case when day_num_of_week = 7 then 'Y' else 'N' end

update dbo.dim_day
set is_last_day_of_month = case when day(dateadd(Day,+1,full_date)) = 1 then 'Y' else 'N' end

update dbo.dim_day
set month_begin_date = 
cast(left(cast(year(full_date) * 100 + month(full_date) as varchar(6)), 4) 
 + '-'
+ right(cast(year(full_date) * 100 + month(full_date) as varchar(6)), 2)
 + '-01' as datetime)

update dbo.dim_day
set month_end_date = dateadd(Day,-1,dateadd(Month, +1, month_begin_date))

update dbo.dim_day
set month_num_of_year = month(full_date)


update dbo.dim_day
set quarter_num_of_year = case when month_num_of_year in (1,2,3) then 1 
                               when month_num_of_year in (4,5,6) then 2
                               when month_num_of_year in (7,8,9) then 3
                               else 4 end 

update d
set quarter_begin_date = s.quarter_begin_date
  , quarter_end_date = s.quarter_end_date
from dbo.dim_day d
inner join
(
 select year_num
      , quarter_num_of_year
      , min(full_date) as quarter_begin_date
      , max(full_date) as quarter_end_date
 from dbo.dim_day s
 group by year_num, quarter_num_of_year
) s
on d.quarter_num_of_year = s.quarter_num_of_year
and d.year_num = s.year_num

update d
set year_begin_date = s.year_begin_date
  , year_end_date = s.year_end_date
from dbo.dim_day d
inner join
(
 select year_num
      , min(full_date) as year_begin_date
      , max(full_date) as year_end_date
 from dbo.dim_day s
 group by year_num
) s
on d.year_num = s.year_num

update dbo.dim_day
set yyyymm = cast(year(full_date) * 100 + month(full_date) as varchar(6))
, yyyymmdd = cast(year(full_date) * 10000 + month(full_date) * 100 + day(full_date) as varchar(8))

update dbo.dim_day
set DDMONYYYY = right(yyyymmdd, 2) + month_name_abbreviation + left(yyyymmdd, 4)


update dbo.dim_day
set quarter_num_overall = DATEDIFF ( q , cast('1900-01-01' as datetime) , full_date ) + 1


update dbo.dim_day
set day_num_of_year =  datepart(dy, full_date)


update dbo.dim_day
set julian_day_num_of_year = year(full_date) * 1000 + datepart(dy, full_date)

update dbo.dim_day
set julian_day_num_absolute = year(full_date) * 1000000.0 + cast(date_key as decimal)

update dbo.dim_day
set is_last_day_of_quarter = 'N'
, is_last_day_of_year = 'N'
, is_last_day_of_fiscal_month = 'N'
, is_last_day_of_fiscal_quarter = 'N'
, is_last_day_of_fiscal_year = 'N'

update dbo.dim_day
set is_last_day_of_quarter = 'Y'
where quarter_end_date = full_date

update dbo.dim_day
set is_last_day_of_year = 'Y'
where year_end_date = full_date

update dbo.dim_day
set week_num_of_year = datepart(wk, full_date)

update d
set week_of_year_begin_date = s.week_of_year_begin_date
  , week_of_year_end_date = s.week_of_year_end_date
from dbo.dim_day d
inner join
(
 select year_num
      , week_num_of_year
      , min(full_date) as week_of_year_begin_date
      , max(full_date) as week_of_year_end_date
 from dbo.dim_day s
 group by year_num, week_num_of_year
) s
on d.year_num = s.year_num
and d.week_num_of_year = s.week_num_of_year


update dbo.dim_day
set week_num_of_month = ceiling(datediff(dd, month_begin_date, full_date) / 7)+1

update d
set week_of_month_begin_date = s.week_of_month_begin_date
  , week_of_month_end_date = s.week_of_month_end_date
from dbo.dim_day d
inner join
(
 select year_num
      , month_num_of_year
      , week_num_of_month
      , min(full_date) as week_of_month_begin_date
      , max(full_date) as week_of_month_end_date
 from dbo.dim_day s
 group by year_num, month_num_of_year, week_num_of_month
) s
on d.year_num = s.year_num
and d.month_num_of_year = s.month_num_of_year
and d.week_num_of_month = s.week_num_of_month


update dbo.dim_day
set week_num_of_quarter = ceiling(datediff(dd, quarter_begin_date, full_date) / 7)+1

update d
set week_of_quarter_begin_date = s.week_of_quarter_begin_date
  , week_of_quarter_end_date = s.week_of_quarter_end_date
from dbo.dim_day d
inner join
(
 select year_num
      , quarter_num_of_year
      , week_num_of_quarter
      , min(full_date) as week_of_quarter_begin_date
      , max(full_date) as week_of_quarter_end_date
 from dbo.dim_day s
 group by year_num, quarter_num_of_year, week_num_of_quarter
) s
on d.year_num = s.year_num
and d.quarter_num_of_year = s.quarter_num_of_year
and d.week_num_of_quarter = s.week_num_of_quarter


update d
set 
  week_of_year_begin_date_key = b.date_key
, week_of_year_end_date_key = e.date_key
from dbo.dim_day d
left outer join dbo.dim_day b
on d.week_of_year_begin_date = b.full_date
left outer join dbo.dim_day e
on d.week_of_year_end_date = e.full_date


update d
set 
  week_of_month_begin_date_key = b.date_key
, week_of_month_end_date_key = e.date_key
from dbo.dim_day d
left outer join dbo.dim_day b
on d.week_of_month_begin_date = b.full_date
left outer join dbo.dim_day e
on d.week_of_month_end_date = e.full_date


update d
set 
  week_of_quarter_begin_date_key = b.date_key
, week_of_quarter_end_date_key = e.date_key
from dbo.dim_day d
left outer join dbo.dim_day b
on d.week_of_quarter_begin_date = b.full_date
left outer join dbo.dim_day e
on d.week_of_quarter_end_date = e.full_date


update d
set 
  month_begin_date_key = b.date_key
, month_end_date_key = e.date_key
from dbo.dim_day d
left outer join dbo.dim_day b
on d.month_begin_date = b.full_date
left outer join dbo.dim_day e
on d.month_end_date = e.full_date


update d
set 
  year_begin_date_key = b.date_key
, year_end_date_key = e.date_key
from dbo.dim_day d
left outer join dbo.dim_day b
on d.year_begin_date = b.full_date
left outer join dbo.dim_day e
on d.year_end_date = e.full_date


update dim_day
set day_num_of_quarter = DATEDIFF(d, quarter_begin_date, full_date) + 1

-- add additional useful date types and keys to handle the "real-world" dates
insert dim_day (date_key, date_type, day_of_week_name, day_of_week_abbreviation)
values (-1, 'UNKNOWN', 'UNKNOWN', 'UNK')

insert dim_day (date_key, date_type, day_of_week_name, day_of_week_abbreviation)
values (-2, 'UNASSIGNED', 'UNASSIGNED', 'NYA')

insert dim_day (date_key, date_type, day_of_week_name, day_of_week_abbreviation)
values (-3, 'INVALID', 'INVALID', 'INV')

/*
Don McMunn is a professional, business-focused, consultant specializing in data warehousing and business intelligence design, development and delivery for over a decade with the Fortune 500 and SMB segments.  He may be reached at:  dmcmunn at ipcdesigns dot com
*/