sellable flag.sql

drop table IF EXISTS sandbox.d_item_status;
create table sandbox.d_item_status(
    item_status_skey bigint,
    is_active varchar(20),
    is_sellable varchar(20)
) DISTSTYLE ALL ;

insert into sandbox.d_item_status
values (1,'is active','is sellable'),
       (2,'is not active','is not sellable'),
       (3,'is not active','is sellable'),
       (4,'is active','is not sellable');

SELECT date_skey,
       fk.shop_skey shop_skey,
       fk.item_skey item_skey,
       fk.kpi_skey kpi_skey,
       dis.item_status_skey item_status_skey,
       fk.value value INTO  sandbox.f_order_kpis
  FROM star_analytical.f_order_kpis fk
       Left JOIN
       star_analytical.f_item_status fs ON fs.period_date_skey = fk.date_skey
and fs.item_skey = fk.item_skey and fs.shop_skey = fk.shop_skey
and fs.period_date_skey between 20200101 and 20200331
LEFT JOIN  sandbox.d_item_status dis
on fs.is_active = dis.is_active
and fs.is_sellable = dis.is_sellable
where fk.date_skey between 20200101 and 20200331