-- SET @pbi_datetime = '2018-01-01 10:00:10'

-- POPULATE DIM_ORDER_DATE
insert into `dim_order_date`
(pk_dim_order_date, full_date, year, quarter, month, month_name, week, day_of_year, day_of_week_sunday_first, day_of_week_monday_first, day_of_week, day_of_month, `inserted_at`)
  select
    s.pk_dim_order_date,
    DATE_FORMAT(s.date_obj, '%m/%d/%Y') as full_date,
    YEAR(date_obj) as year,
    QUARTER(date_obj) as quarter,
    MONTH(date_obj) as month,
    MONTHNAME(date_obj) as month_name,
    WEEK(date_obj) as week,
    DAYOFYEAR(date_obj) as day_of_year,
    DAYOFWEEK(date_obj) as day_of_week_sunday_first,
    WEEKDAY(date_obj) + 1 as day_of_week_monday_first,
    WEEKDAY(date_obj) + 1 as day_of_week,
    DAYOFMONTH(date_obj) as day_of_month,
    @db_date_time as inserted_at
  from (
    select
      CAST(`date` AS SIGNED) as pk_dim_order_date,
      STR_TO_DATE(`date`, '%Y%m%d') as date_obj
    from `bim_stg_date`
    WHERE CAST(`date` AS SIGNED) > (SELECT COALESCE(max(`pk_dim_order_date`), 0) FROM `dim_order_date`)
  ) as s
order by s.pk_dim_order_date ASC;

-- {{IF_ANALYZE}}dim_order_date{{/IF_ANALYZE}}