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

-- POPULATE dim_tax_rate
select @max_inserted_at:=coalesce(max(stg_inserted_at), FROM_UNIXTIME(1)) from dim_tax_rate;
insert into `dim_tax_rate`
(pk_dim_tax_rate, tax_rate_name, tax_rate_code, percent, `stg_inserted_at`, `inserted_at`)
SELECT
  s.pk_dim_tax_rate,
  n.title as tax_rate_name,
  s.tax_rate_code,
  s.percent,
  s.inserted_at as stg_inserted_at,
  @db_date_time as inserted_at
FROM (
    SELECT
      min(t.tax_id) as pk_dim_tax_rate,
      max(t.tax_id) as latest_tax_id,
      t.code AS tax_rate_code,
      t.percent/100 AS percent,
      max(t.inserted_at) as inserted_at
    FROM bim_stg_order_tax t
      where t.inserted_at >= @max_inserted_at
    GROUP BY t.code, t.percent) s
  join bim_stg_order_tax n
    on n.tax_id = s.latest_tax_id
  left join dim_tax_rate dim
    on dim.tax_rate_code = s.tax_rate_code
    AND dim.percent = s.percent
WHERE dim.pk_dim_tax_rate is NULL
order by stg_inserted_at;

-- {{IF_ANALYZE}}dim_tax_rate{{/IF_ANALYZE}}
