-- (1)
-- DIMStoreCustomer
-- Selects store customers only
-- Returns CustomerID in range [1, 701]
SELECT c.customerID, c.PersonID, c.StoreID, s.Name, cr.Name AS CountryName,
    cr.CountryRegionCode as CountryCode, sp.StateProvinceCode AS Region
INTO DIMStoreCustomer
FROM AdventureWorks2012.Sales.Customer AS c
    JOIN AdventureWorks2012.Sales.Store AS s ON c.StoreID = s.BusinessEntityID
    JOIN AdventureWorks2012.Person.BusinessEntityAddress AS ba ON s.BusinessEntityID = ba.BusinessEntityID
    JOIN AdventureWorks2012.Person.Address AS a ON a.AddressID = ba.AddressID
    JOIN AdventureWorks2012.Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID
    JOIN AdventureWorks2012.Person.CountryRegion AS cr ON sp.CountryRegionCode = cr.CountryRegionCode
where c.PersonID IS NULL



-- (2)
-- FactTable
-- Returns CustomerID in range [11000, 30118]
SELECT h.OrderDate, h.ShipDate, d.OrderQty as OrderQuantity, d.UnitPrice, d.UnitPriceDiscount, d.LineTotal,
    h.CustomerID as IndividualCustomer, h.CustomerID as StoreCustomer, d.ProductID, h.SalesPersonID
INTO FactTable
FROM AdventureWorks2012.Sales.SalesOrderHeader AS h
    JOIN AdventureWorks2012.Sales.SalesOrderDetail AS d ON d.SalesOrderID = h.SalesOrderID



-- (3)
-- Filtering StoreCustomer in FactTable so that it contains StoreCustomer IDs only
UPDATE FactTable
SET FactTable.StoreCustomer = NULL
WHERE StoreCustomer NOT IN (
    SELECT DISTINCT f.StoreCustomer from FactTable f
    JOIN AdventureWorks2012.Sales.Customer c on F.StoreCustomer = c.CustomerID
    JOIN AdventureWorks2012.Sales.Store s on s.BusinessEntityID = c.StoreID)
-- (60398 row(s) affected)