Create a date dimension table in AWS Redshift - from 1985 to 2060

dim_date.sql

begin transaction;

drop table if exists numbers;
create temp table numbers as (
    with recursive
        counts (number) as (
            select 1 as number
            union all
            select number + 1
            from counts m
            where number < 27758 -- [!] running dates from 1985-01-01 to 2060-12-31 -- edit here if needed [!]
        )

    select number
    from counts
);


drop table if exists public.dim_date cascade;
create table public.dim_date (
    "surrogate_key" int4,
    "date" date,
    "day_of_week" float8,
    "day_of_week_name" varchar(9),
    "day_of_month" int4,
    "day_of_month_name" varchar(4),
    "day_of_year" int4,
    "day_of_year_name" varchar(5),
    "week" int4,
    "iso_week" int4,
    "full_week" int4,
    "week_name" varchar(4),
    "week_end_date" timestamp null,
    "week_start_date" timestamp null,
    "month" int4,
    "month_name" varchar(9),
    "month_end_date" timestamp null,
    "month_start_date" timestamp null,
    "quarter" int4,
    "quarter_name" varchar(2),
    "half_year" int4,
    "half_year_name" varchar(2),
    "year" int4,
    "year_end_date" timestamp null,
    "year_start_date" timestamp null,
    "is_weekday" boolean,
    "is_weekend" boolean,
    "plus_7" date,
    "plus_14" date,
    "plus_21" date,
    "plus_28" date,
    "plus_35" date,
    "plus_42" date,
    "plus_49" date,
    "plus_56" date,
    "plus_63" date,
    "plus_70" date,
    "plus_77" date,
    "plus_84" date,
    "plus_91" date,
    "minus_7" date,
    "minus_14" date,
    "minus_21" date,
    "minus_28" date,
    "minus_35" date,
    "minus_42" date,
    "minus_49" date,
    "minus_56" date,
    "minus_63" date,
    "minus_70" date,
    "minus_77" date,
    "minus_84" date,
    "minus_91" date,
    "month_day_name_rank" int4,
    "month_day_name_reverse_rank" int4,
    "us_holiday_identifier" varchar(30),
    "is_business_day" boolean
) diststyle all sortkey (date);

insert into public.dim_date
(
    surrogate_key,
    "date",
    day_of_week,
    day_of_week_name,
    day_of_month,
    day_of_month_name,
    day_of_year,
    day_of_year_name,
    week,
    iso_week,
    week_name,
    week_end_date,
    week_start_date,
    "month",
    month_name,
    month_end_date,
    month_start_date,
    quarter,
    quarter_name,
    half_year,
    half_year_name,
    "year",
    year_end_date,
    year_start_date,
    is_weekday,
    is_weekend
)

with
    numbers as (
        select *
        from numbers
    ),

    bas as (
        select
            convert(
                int, to_char(dateadd(day, num.number, '1985-01-01'), 'YYYYMMDD')
            ) as surrogate_key,
            cast(dateadd(day, num.number, '1985-01-01') as date) as "date",
            date_part(dow, dateadd(day, num.number, '1985-01-01'))
            + 1 as day_of_week,
            datepart(
                day, dateadd(day, num.number, '1985-01-01')
            ) as day_of_month,
            datepart(
                doy, dateadd(day, num.number, '1985-01-01')
            ) as day_of_year,
            datepart(week, dateadd(day, num.number, '1985-01-01')) as week,
            datepart(month, dateadd(day, num.number, '1985-01-01')) as "month",
            datepart(
                quarter, dateadd(day, num.number, '1985-01-01')
            ) as quarter,
            case
                when datepart(qtr, dateadd(day, num.number, '1985-01-01')) < 3
                then 1
                else 2
            end as half_year,
            datepart(year, dateadd(day, num.number, '1985-01-01')) as "year",
            case
                when
                    datepart(dow, dateadd(day, num.number, '1985-01-01')) in (
                        0, 6
                    )
                then 0
                else 1
            end as is_weekday,
            case
                when
                    datepart(dow, dateadd(day, num.number, '1985-01-01')) in (
                        0, 6
                    )
                then 1
                else 0
            end as is_weekend
        from numbers as num
    )

select
    bas.surrogate_key,
    bas.date,
    bas.day_of_week,
    case bas.day_of_week
        when 1 then 'Sunday'
        when 2 then 'Monday'
        when 3 then 'Tuesday'
        when 4 then 'Wednesday'
        when 5 then 'Thursday'
        when 6 then 'Friday'
        when 7 then 'Saturday'
    end as day_of_week_name,
    bas.day_of_month,
    convert(varchar(2), bas.day_of_month)
    + case right(convert(varchar(2), bas.day_of_month), 1)
        when 1
        then
            case
                when convert(varchar(2), bas.day_of_month) = '11'
                then 'th'
                else 'st'
            end
        when 2
        then
            case
                when convert(varchar(2), bas.day_of_month) = '12'
                then 'th'
                else 'nd'
            end
        when 3
        then
            case
                when convert(varchar(2), bas.day_of_month) = '13'
                then 'th'
                else 'rd'
            end
        when 4 then 'th'
        when 5 then 'th'
        when 6 then 'th'
        when 7 then 'th'
        when 8 then 'th'
        when 9 then 'th'
        when 0 then 'th'
    end as day_of_month_name,
    bas.day_of_year,
    convert(varchar(3), bas.day_of_year)
    + case right(convert(varchar(2), bas.day_of_year), 1)
        when 1
        then
            case
                when right(convert(varchar(2), bas.day_of_year), 2) = '11'
                then 'th'
                else 'st'
            end
        when 2
        then
            case
                when right(convert(varchar(2), bas.day_of_year), 2) = '12'
                then 'th'
                else 'nd'
            end
        when 3
        then
            case
                when right(convert(varchar(2), bas.day_of_year), 2) = '13'
                then 'th'
                else 'rd'
            end
        when 4 then 'th'
        when 5 then 'th'
        when 6 then 'th'
        when 7 then 'th'
        when 8 then 'th'
        when 9 then 'th'
        when 0 then 'th'
    end as day_of_year_name,
    cast(to_char(bas.date, 'WW') as int) as week,
    cast(to_char(bas.date, 'IW') as int) as iso_week,
    convert(varchar(2), bas.week)
    + case right(convert(varchar(2), bas.week), 1)
        when 1
        then
            case
                when convert(varchar(2), bas.week) = '11'
                then 'th'
                else 'st'
            end
        when 2
        then
            case
                when convert(varchar(2), bas.week) = '12'
                then 'th'
                else 'nd'
            end
        when 3
        then
            case
                when convert(varchar(2), bas.week) = '13'
                then 'th'
                else 'rd'
            end
        when 4 then 'th'
        when 5 then 'th'
        when 6 then 'th'
        when 7 then 'th'
        when 8 then 'th'
        when 9 then 'th'
        when 0 then 'th'
    end as week_name,
    dateadd(
        day, 7 - (convert(int, bas.day_of_week)), bas.date
    ) as week_end_date,
    date_trunc('week', bas.date) as week_start_date,
    bas.month,
    case bas.month
        when 1 then 'January'
        when 2 then 'February'
        when 3 then 'March'
        when 4 then 'April'
        when 5 then 'May'
        when 6 then 'June'
        when 7 then 'July'
        when 8 then 'August'
        when 9 then 'September'
        when 10 then 'October'
        when 11 then 'November'
        when 12 then 'December'
    end as month_name,
    last_day(bas.date) as month_end_date,
    date_trunc('month', bas.date) as month_start_date,
    bas.quarter,
    'Q' + convert(varchar(1), bas.quarter) as quarter_name,
    bas.half_year,
    'H' + convert(varchar(1), bas.half_year) as half_year_name,
    bas.year,
    dateadd(
        day, -1, dateadd(year, +1, date_trunc('year', bas.date))
    ) as year_end_date,
    date_trunc('year', bas.date) as year_start_date,
    bas.is_weekday,
    bas.is_weekend
from bas;


update public.dim_date
set
    plus_7 = dateadd(day, 7, "date"),
    plus_14 = dateadd(day, 14, "date"),
    plus_21 = dateadd(day, 21, "date"),
    plus_28 = dateadd(day, 28, "date"),
    plus_35 = dateadd(day, 35, "date"),
    plus_42 = dateadd(day, 42, "date"),
    plus_49 = dateadd(day, 49, "date"),
    plus_56 = dateadd(day, 56, "date"),
    plus_63 = dateadd(day, 63, "date"),
    plus_70 = dateadd(day, 70, "date"),
    plus_77 = dateadd(day, 71, "date"),
    plus_84 = dateadd(day, 84, "date"),
    plus_91 = dateadd(day, 91, "date"),
    minus_7 = dateadd(day, -7, "date"),
    minus_14 = dateadd(day, -14, "date"),
    minus_21 = dateadd(day, -21, "date"),
    minus_28 = dateadd(day, -28, "date"),
    minus_35 = dateadd(day, -35, "date"),
    minus_42 = dateadd(day, -42, "date"),
    minus_49 = dateadd(day, -49, "date"),
    minus_56 = dateadd(day, -56, "date"),
    minus_63 = dateadd(day, -63, "date"),
    minus_70 = dateadd(day, -70, "date"),
    minus_77 = dateadd(day, -71, "date"),
    minus_84 = dateadd(day, -84, "date"),
    minus_91 = dateadd(day, -91, "date")
where "date" < '2060-12-31';

drop table if exists tt_month_rank;
create temp table tt_month_rank as
select
    dim_date.date,
    row_number()
        over (
            partition by year, month, day_of_week_name
            order by date
        )
    as month_day_name_rank,
    row_number()
        over (
            partition by year, month, day_of_week_name
            order by date desc
        )
    as month_day_name_reverse_rank
from dim_date;

update public.dim_date
set
    month_day_name_rank = tt_month_rank.month_day_name_rank,
    month_day_name_reverse_rank = tt_month_rank.month_day_name_reverse_rank
from tt_month_rank
where tt_month_rank.date = dim_date.date;


update public.dim_date
set
    "us_holiday_identifier" =
    case
        when month_name = 'January' and day_of_month = 1
        then 'New Years Day'
        when
            month_name = 'May'
            and day_of_week_name = 'Monday'
            and month_day_name_reverse_rank = 1
        then 'Memorial Day'
        when month_name = 'July' and day_of_month = 4
        then '4th of July/Independence Day'
        when
            month_name = 'September'
            and day_of_week_name = 'Monday'
            and month_day_name_rank = 1
        then 'Labor Day'
        when
            month_name = 'October'
            and day_of_week_name = 'Monday'
            and month_day_name_rank = 2
        then 'Columbus Day'
        when month_name = 'November' and day_of_month = 11
        then 'Veterans Day'
        when
            month_name = 'November'
            and day_of_week_name = 'Thursday'
            and month_day_name_rank = 4
        then 'Thanksgiving'
        when month_name = 'December' and day_of_month = 25
        then 'Christmas'
    end;


drop table if exists tt_full_weeks_per_year;
create temporary table tt_full_weeks_per_year as
with
    days_per_week_per_year as (
        select
            "year",
            "week",
            count(1) days
        from public.dim_date
        where "date" between '1985-01-01' and '2060-12-31'
        group by "year", "week"
    )

select
    "year",
    "week",
    row_number()
        over (
            partition by "year"
            order by "week"
        )
    full_week
from days_per_week_per_year
where days = 7;

update public.dim_date
set full_week = tt_full_weeks_per_year.full_week
from tt_full_weeks_per_year
where
    tt_full_weeks_per_year."year" = dim_date."year" and tt_full_weeks_per_year.week = dim_date.week;


update public.dim_date
set
    is_business_day =
    case
        when "us_holiday_identifier" is not null or is_weekend then false
        else true
    end
;

commit transaction;