--**********************************************************************
-- Script to populate the Customer1, Store1, Product1 and Time1 tables
-- with unique data and drop the staging tables.
-- Last modified - 12/7/02 - ACD
--**********************************************************************

-- Customer1 data load

DELETE FROM Customer1

INSERT INTO Customer1
SELECT DISTINCT a11.customer, Max(a11.gender), Max(a11.dob) 
FROM customer a11 
JOIN customer a12 ON 
a11.customer = a12.customer
WHERE a11.dob = a12.dob
GROUP BY a11.customer

GO

--Product1 Data Load

DELETE FROM Product1

INSERT INTO Product1
SELECT DISTINCT Product_ID, Manufacturer,Brand, Cat2, Cat4, Cat6
FROM Product
WHERE Product_ID IS NOT NULL

GO

-- Store1 Data Load

DELETE FROM Store1

INSERT INTO Store1
SELECT DISTINCT Store, Location 
FROM Store
WHERE store IS NOT NULL

GO

-- Time Data Load

DELETE FROM Time1

INSERT INTO Time1
SELECT DISTINCT Cast(SUBSTRING(Time_Key, 1, 8) + SUBSTRING(Time_Key, 10, 4) as numeric) 
, Time, Date, Day , Week_Number, Week_Begin_Date 
FROM Time
WHERE Time_Key IS NOT NULL 

GO

-- Sales data load

DELETE FROM Sales1
INSERT INTO Sales1
SELECT a11.Customer
, a11.Product_ID
, a11.Store
, a11.Receipt
, a15.Time_Key
, Sum(a11.Quantity)
, Sum(a11.Cost)
, Sum(a11.Discount)
, Sum(a11.Total_Profit)
, Max(a11.Buy_not)
FROM Sales a11
JOIN Customer1 a12 ON
a11.Customer = a12.Customer
JOIN Store1 a14 ON
a11.Store = a14.Store
JOIN Product1 a13 ON
a11.Product_ID = a13.Product_ID
JOIN Time1 a15 ON
a15.Time_Key = Cast(SUBSTRING(a11.Time_Key, 1, 8) + SUBSTRING(a11.Time_Key, 10, 4) as numeric) 
GROUP BY a11.Customer
, a11.Product_ID
, a11.Store
, a11.Receipt
, a15.Time_Key

-- Drop all staging tables
DROP TABLE Time
DROP TABLE Store
DROP Table Sales
DROP Table Customer
DROP TABLE Product

-- *************END OF SCRIPT EXECUTION**************