114 lines
3.3 KiB
SQL
Executable File
114 lines
3.3 KiB
SQL
Executable File
--
|
|
--
|
|
-- Updates to make dates 2006-2007
|
|
--
|
|
--
|
|
|
|
UPDATE time_by_day SET the_year = 2006 WHERE the_year = 1997;
|
|
|
|
UPDATE time_by_day SET the_year = 2007 WHERE the_year = 1998;
|
|
|
|
|
|
ALTER TABLE time_by_day ADD day_of_week int;
|
|
|
|
|
|
UPDATE time_by_day SET day_of_week = 1 WHERE the_day = 'Monday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 2 WHERE the_day = 'Tuesday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 3 WHERE the_day = 'Wednesday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 4 WHERE the_day = 'Thursday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 5 WHERE the_day = 'Friday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 6 WHERE the_day = 'Saturday';
|
|
|
|
UPDATE time_by_day SET day_of_week = 7 WHERE the_day = 'Sunday';
|
|
|
|
UPDATE agg_c_10_sales_fact_1997 SET the_year = 2006 WHERE the_year = 1997;
|
|
UPDATE agg_c_14_sales_fact_1997 SET the_year = 2006 WHERE the_year = 1997;
|
|
UPDATE agg_c_special_sales_fact_1997 SET time_year = 2006 WHERE time_year = 1997;
|
|
UPDATE agg_lc_100_sales_fact_1997 SET the_year = 2006 WHERE the_year = 1997;
|
|
|
|
CREATE TABLE
|
|
monthly_profit AS
|
|
SELECT
|
|
min(time_by_day.the_date) as the_date, /* we retrieve the first day of each month */
|
|
time_by_day.the_year AS the_year,
|
|
time_by_day.the_month AS the_month,
|
|
time_by_day.quarter AS the_quarter,
|
|
time_by_day.month_of_year AS time_by_day_month_of_year,
|
|
sum(sales_fact_1997.store_sales) AS store_sales,
|
|
sum(sales_fact_1997.store_sales)-sum(sales_fact_1997.store_cost) AS profit,
|
|
store.store_state AS store_state
|
|
FROM
|
|
sales_fact_1997 sales_fact_1997 INNER JOIN store store ON sales_fact_1997.store_id = store.store_id
|
|
INNER JOIN time_by_day time_by_day ON sales_fact_1997.time_id = time_by_day.time_id
|
|
WHERE
|
|
store.store_state <> 'NULL' /* when param PickState contains ALL use NULL otherwise use PickState */
|
|
GROUP BY
|
|
time_by_day.the_year,
|
|
time_by_day.the_month,
|
|
time_by_day.quarter,
|
|
time_by_day.month_of_year,
|
|
store.store_state
|
|
ORDER BY
|
|
time_by_day.month_of_year ASC;
|
|
|
|
CREATE TABLE
|
|
product_sales AS
|
|
SELECT
|
|
product.product_name AS product_product_name,
|
|
sum(sales_fact_1997.store_sales)AS store_sales,
|
|
product.product_id AS product_product_id
|
|
FROM
|
|
sales_fact_1997 sales_fact_1997,
|
|
product product
|
|
WHERE
|
|
sales_fact_1997.product_id = product.product_id
|
|
GROUP BY
|
|
product.product_name,
|
|
product.product_id
|
|
ORDER BY
|
|
store_sales DESC;
|
|
|
|
CREATE TABLE
|
|
customer_sales AS
|
|
SELECT
|
|
customer.fullname AS customer_fullname,
|
|
sum(sales_fact_1997.store_sales)AS store_sales,
|
|
customer.customer_id AS customer_id
|
|
FROM
|
|
sales_fact_1997 sales_fact_1997,
|
|
customer customer
|
|
WHERE
|
|
sales_fact_1997.customer_id = customer.customer_id
|
|
GROUP BY
|
|
customer.fullname,
|
|
customer.customer_id
|
|
ORDER BY
|
|
store_sales DESC;
|
|
|
|
CREATE TABLE
|
|
promotion_sales AS
|
|
SELECT
|
|
promotion.promotion_name AS promotion_promotion_name,
|
|
sum(sales_fact_1997.store_sales)AS store_sales,
|
|
promotion.promotion_id AS promotion_promotion_id
|
|
FROM
|
|
sales_fact_1997 sales_fact_1997,
|
|
promotion promotion
|
|
WHERE
|
|
sales_fact_1997.promotion_id = promotion.promotion_id
|
|
GROUP BY
|
|
promotion.promotion_name,
|
|
promotion.promotion_id
|
|
HAVING
|
|
promotion.promotion_name <> 'No Promotion'
|
|
ORDER BY
|
|
store_sales DESC;
|
|
|
|
|
|
|