Skip to content

Instantly share code, notes, and snippets.

@hirosumee

hirosumee/gist:443e5adb6fd8f9e862ae0587a63c1d0f

Created July 7, 2020 07:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hirosumee/443e5adb6fd8f9e862ae0587a63c1d0f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hirosumee/443e5adb6fd8f9e862ae0587a63c1d0f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
create database warehouse;
go;
use warehouse;
drop table if exists fact_sale_tbl;
drop table if exists time_tbl;
drop table if exists customer_tbl;
drop table if exists store_tbl;
drop table if exists stored_merchan_tbl;
drop table if exists merchandise_tbl;
go;
create table time_tbl
(
id INT PRIMARY KEY IDENTITY (1, 1),
day INT,
month INT,
quarter INT,
year INT,
full_date date
)
create table customer_tbl
(
id INT PRIMARY KEY,
type NVARCHAR(30),
country_code INT,
name NVARCHAR(400),
first_ordered_at DATE,
courier NVARCHAR(200),
address NVARCHAR(200),
sign_up_at date,
--CONSTRAINT fk_cs_stime FOREIGN KEY (sign_up_at) REFERENCES time_tbl(id),
)
create table store_tbl
(
id INT PRIMARY KEY,
phone NVARCHAR(200),
country_code INT,
office_name NVARCHAR(400),
office_address NVARCHAR(400),
state NVARCHAR(100),
store_created_at date,
office_created_at date,
--CONSTRAINT fk_s_stime FOREIGN KEY (store_created_at) REFERENCES time_tbl(id),
--CONSTRAINT fk_s_otime FOREIGN KEY (office_created_at) REFERENCES time_tbl(id),
);
create table merchandise_tbl
(
id INT PRIMARY KEY,
description NVARCHAR(500),
sz NVARCHAR(100),
weight INT,
price INT,
producted_at date
--CONSTRAINT fk_m_time FOREIGN KEY (producted_at) REFERENCES time_tbl(id),
);
create table stored_merchan_tbl
(
id INT PRIMARY KEY IDENTITY (1, 1),
store_id INT,
merchan_id INT,
quatity INT,
stored_at date
CONSTRAINT fk_sm_store FOREIGN KEY (store_id) REFERENCES store_tbl (id),
CONSTRAINT fk_sm_merchan FOREIGN KEY (merchan_id) REFERENCES merchandise_tbl (id),
--CONSTRAINT fk_sm_time FOREIGN KEY (stored_at) REFERENCES time_tbl(id),
);
create table fact_sale_tbl
(
id INT PRIMARY KEY IDENTITY (1, 1),
time_id INT,
store_id INT,
mechandise_id INT,
order_id INT,
customer_id INT,
quantity INT,
price FLOAT,
total_price FLOAT
CONSTRAINT fk_fs_time FOREIGN KEY (time_id) REFERENCES time_tbl (id),
CONSTRAINT fk_fs_mechan FOREIGN KEY (mechandise_id) REFERENCES merchandise_tbl (id),
CONSTRAINT fk_fs_custo FOREIGN KEY (customer_id) REFERENCES customer_tbl (id),
CONSTRAINT fk_fs_stor FOREIGN KEY (store_id) REFERENCES store_tbl (id)
);
go;
@hirosumee
Copy link
Author

hirosumee commented Jul 7, 2020 

CREATE UNIQUE NONCLUSTERED INDEX [UIX_tbltime_day_month_year]
    ON warehouse.dbo.time_tbl
        ([day] ASC, [month] ASC, [year] ASC) WITH (IGNORE_DUP_KEY = ON);
go;
INSERT warehouse.dbo.time_tbl (year, month, day, quarter, full_date)
    (SELECT YEAR(cus.time)        year,
            MONTH(cus.time)       month,
            DAY(cus.time)         day,
            (MONTH(cus.time) / 3) quarter,
            cus.time
     from cus_order cus
    );

select count(id)
from warehouse.dbo.time_tbl;

INSERT warehouse.dbo.customer_tbl(id, country_code, name, courier, first_ordered_at, sign_up_at, type, address)
    (select c.id                                                    id,
            c.city_code                                             country_code,
            c.name                                                  name,
            tc.courier                                              courier,
            c.first_order_at                                        first_order_at,
            iif(tc.time is not null, tc.time, pc.time)              time,
            iif(tc.cus_id is not null and pc.cus_id is not null, 'dual',
                iif(tc.cus_id is not null, 'travel', 'postoffice')) type,
            pc.office_address                                       address
     from customer c
              full join travel_customer tc on c.id = tc.cus_id
              full join postoffice_customer pc on c.id = pc.cus_id);


-- INSERT warehouse.dbo.customer_tbl(id, country_code, name, address, first_ordered_at, sign_up_at, type)
--     (select     c.id + 50         id,
--                 c.city_code       country_code,
--                 c.name            name,
--                 pc.office_address address,
--                 c.first_order_at  first_order_at,
--                 pc.time           sign_up_at,
--          type = 'postoffice'
--      from customer c
--               join postoffice_customer pc on c.id = pc.cus_id);

INSERT warehouse.dbo.store_tbl(id, phone, country_code, office_name, office_address, state, store_created_at,
                               office_created_at) (select s.id      id,
                                                          s.phone   phone,
                                                          o.id      country_code,
                                                          o.name    office_name,
                                                          o.address office_address,
                                                          o.state   state,
                                                          s.time    store_created_at,
                                                          o.time    office_created_at
                                                   from store s
                                                            inner join office o on s.office_id = o.id)


insert warehouse.dbo.merchandise_tbl (id, description, sz, weight, price, producted_at) (select * from merchandise);

insert warehouse.dbo.stored_merchan_tbl (store_id, merchan_id, quatity, stored_at) (select store_id, merchandise_id, quantity, time from stored_merchan)


insert warehouse.dbo.fact_sale_tbl (customer_id, mechandise_id, order_id, store_id, quantity, price, total_price,
                                    time_id)
    (select od.cus_id                    customer_id,
            pm.merchan_id                mechandise_id,
            od.id                        order_id,
            st.id                        store_id,
            sm.quantity                  quantity,
            mer.price,
            sm.quantity * mer.price      total,
            (select top 1 t.id
             from warehouse.dbo.time_tbl t
             where t.year = YEAR(od.time)
               AND t.month = MONTH(od.time)
               AND t.day = DAY(od.time)) time_id
     from cus_order od
              inner join placed_merchan pm on pm.order_id = od.id
              inner join stored_merchan sm on sm.merchandise_id = pm.merchan_id
              inner join merchandise mer on mer.id = pm.merchan_id
              inner join store st on st.id = store_id where exists(select * from warehouse.dbo.customer_tbl wdc where wdc.id = od.cus_id ));

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment