You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
128 lines
3.1 KiB
128 lines
3.1 KiB
DROP TABLE IF EXISTS article_color_lookup;
|
|
CREATE TABLE article_color_lookup (
|
|
article_code INT NOT NULL,
|
|
color_code INT NOT NULL,
|
|
article_label VARCHAR(45),
|
|
color_label VARCHAR(30),
|
|
category VARCHAR(25),
|
|
sale_price DECIMAL(8,2),
|
|
family_name VARCHAR(20),
|
|
family_code VARCHAR(3),
|
|
PRIMARY KEY (article_code, color_code)
|
|
);
|
|
|
|
LOAD DATA LOCAL INFILE './emode/article_color_lookup.csv' INTO TABLE article_color_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
|
|
(
|
|
article_code,
|
|
color_code,
|
|
article_label,
|
|
color_label,
|
|
category,
|
|
sale_price,
|
|
family_name,
|
|
family_code
|
|
);
|
|
|
|
DROP TABLE IF EXISTS article_lookup;
|
|
CREATE TABLE article_lookup (
|
|
article_code INT NOT NULL,
|
|
article_label VARCHAR(45),
|
|
category VARCHAR(25),
|
|
sale_price DECIMAL(8,2),
|
|
family_name VARCHAR(20),
|
|
family_code VARCHAR(3),
|
|
PRIMARY KEY (article_code)
|
|
);
|
|
|
|
LOAD DATA LOCAL INFILE './emode/article_lookup.csv' INTO TABLE article_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (
|
|
article_code,
|
|
article_label,
|
|
category,
|
|
sale_price,
|
|
family_name,
|
|
family_code
|
|
);
|
|
|
|
DROP TABLE IF EXISTS calendar_year_lookup;
|
|
CREATE TABLE calendar_year_lookup (
|
|
week_key INT NOT NULL,
|
|
week_in_year INT NOT NULL,
|
|
year INT NOT NULL,
|
|
fiscal_period VARCHAR(4),
|
|
year_week VARCHAR(7) NOT NULL,
|
|
quarter INT NOT NULL,
|
|
month_name VARCHAR(10) NOT NULL,
|
|
month INT NOT NULL,
|
|
holiday_flag VARCHAR(1),
|
|
PRIMARY KEY (week_key)
|
|
);
|
|
|
|
LOAD DATA LOCAL INFILE './emode/calendar_year_lookup.csv' INTO TABLE calendar_year_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
|
|
(
|
|
week_key,
|
|
week_in_year,
|
|
year,
|
|
fiscal_period,
|
|
year_week,
|
|
quarter,
|
|
month_name,
|
|
month,
|
|
holiday_flag
|
|
);
|
|
|
|
DROP TABLE IF EXISTS outlet_lookup;
|
|
CREATE TABLE outlet_lookup (
|
|
shop_name VARCHAR(30),
|
|
address_1 VARCHAR(20),
|
|
manager VARCHAR(10),
|
|
date_open DATE NOT NULL,
|
|
open VARCHAR(1),
|
|
owned_outright VARCHAR(1),
|
|
floor_space INT,
|
|
zip_code VARCHAR(6),
|
|
city VARCHAR(20),
|
|
state VARCHAR(20),
|
|
shop_code INT NOT NULL,
|
|
PRIMARY KEY (shop_code)
|
|
);
|
|
|
|
LOAD DATA LOCAL INFILE './emode/outlet_lookup.csv' INTO TABLE outlet_lookup FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
|
|
(
|
|
shop_name,
|
|
address_1,
|
|
manager,
|
|
@date_open,
|
|
open,
|
|
owned_outright,
|
|
floor_space,
|
|
zip_code,
|
|
city,
|
|
state,
|
|
shop_code
|
|
)
|
|
SET date_open = STR_TO_DATE(@date_open, "%d-%b-%y");
|
|
|
|
DROP TABLE IF EXISTS shop_facts;
|
|
CREATE TABLE shop_facts (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
article_code INT NOT NULL,
|
|
color_code INT NOT NULL,
|
|
week_key INT NOT NULL,
|
|
shop_code INT NOT NULL,
|
|
margin DECIMAL(13,2) NOT NULL,
|
|
amount_sold DECIMAL(13,2) NOT NULL,
|
|
quantity_sold DECIMAL(13,2) NOT NULL,
|
|
PRIMARY KEY (id)
|
|
);
|
|
|
|
LOAD DATA LOCAL INFILE './emode/shop_facts.csv' INTO TABLE shop_facts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (
|
|
id,
|
|
article_code,
|
|
color_code,
|
|
week_key,
|
|
shop_code,
|
|
margin,
|
|
amount_sold,
|
|
quantity_sold
|
|
);
|