Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- (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)
- -- Returns CustomerID in range [29487, 30114]
- -- 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)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement