SCD 2 procedure

hw3.sql

-- source tables
drop table if exists customers;
drop table if exists customer_type;
drop table if exists dim_customer;
drop function if exists update_dim_customer();

create table customer_type (
  type_id          int primary key,
  type_description text
);

create table customers (
  customer_id  int primary key,
  fullname     varchar(100),
  branch       varchar(100),
  type_id      int references customer_type (type_id) not null,
  national_id  varchar(20),
  occupation   varchar(100),
  phone_number varchar(15)
);

-- dimensions
create table dim_customer (
  customer_key             serial primary key, -- surrogate key
  customer_id              int     not null, -- original key
  name                     varchar(200),
  branch                   varchar(200),
  customer_type_id         int,
  customer_type_descrption text,
  national_id              varchar(40),
  phone_number             varchar(30),
  -- occupation is of scd type 2
  occupation               varchar(200),
  occ_startdate            date,
  occ_enddate              date,
  occ_flag                 boolean not null
);

create function update_dim_customer()
  returns table(
    update_rows_affected integer,
    insert_rows_affected integer
  )
language plpgsql as $$
declare
  update_affected integer;
  insert_affected integer;
begin
  -- temp table holding all user info
  drop table if exists temp_cust;
  create temp table temp_cust as
    select
      c1.customer_id,
      c1.fullname         as name,
      c1.branch,
      c2.type_id          as customer_type_id,
      c2.type_description as customer_type_description,
      c1.national_id,
      c1.phone_number,
      c1.occupation
    from customers c1 inner join customer_type c2 on c1.type_id = c2.type_id;

  -- update existing
  update dim_customer as dim
  set
    occ_enddate = now(),
    occ_flag    = FALSE
  from temp_cust
  where dim.customer_id = temp_cust.customer_id
        and dim.occupation != temp_cust.occupation
        and dim.occ_enddate is null
        and dim.occ_flag = TRUE;

  -- especial postgres variable
  GET DIAGNOSTICS update_affected := ROW_COUNT;

  -- insert new rows where new occ
  insert into dim_customer (customer_id,
                            name,
                            branch,
                            customer_type_id,
                            customer_type_descrption,
                            national_id,
                            phone_number,
                            occupation,
                            occ_startdate,
                            occ_enddate,
                            occ_flag)
    select
      customer_id,
      name,
      branch,
      customer_type_id,
      customer_type_description,
      national_id,
      phone_number,
      occupation,
      now(),
      null,
      TRUE --flag
    from temp_cust
    where not exists(
        select customer_id
        from dim_customer as dim
        where dim.customer_id = temp_cust.customer_id
              and dim.occupation = temp_cust.occupation
    );
  GET DIAGNOSTICS insert_affected := ROW_COUNT;

  return query select
                 update_affected,
                 insert_affected;
end;
$$;

insert into customer_type (type_id, type_description) values (1, 'type A'), (2, 'type B');

-- first
insert into customers (customer_id, fullname, branch, type_id, national_id, occupation, phone_number) values
  (100, 'Ali Haghani', 'isfahan central', 1, '90011111', 'salesperson', '9130000000'),
  (200, 'Reza Mohammadi', 'tehran', 2, '90011111', 'doctor', '9121111111');

select *
from update_dim_customer();

select *
from dim_customer;
-- change occ
update customers
set
  occupation = 'manager'
where customer_id = 100;

insert into customers (customer_id, fullname, branch, type_id, national_id, occupation, phone_number) values
  (300, 'Fateme Alimardani', 'isfahan', 1, '19222222', 'engineer', '9132222222'),
  (400, 'Hossein Raad', 'shiraz', 2, '192222222222', '', '9154444444');


select *
from update_dim_customer();

select *
from dim_customer;
-- second update
update customers
set
  occupation = 'CEO'
where customer_id = 100;

update customers
set
  occupation = 'lead engineer'
where customer_id = 300;

select *
from update_dim_customer();

select *
from dim_customer
order by customer_id, occ_flag;

-- insert into customers (customer_id, fullname, branch, type_id, national_id, occupation, phone_number) values
--   (500, 'fifth customer', 'somewhere', 1, '', 'job 5', ''),
--   (600, ' sixth customer', 'somewhere else', 2, '', 'job 6', '');