SELECT	sod.SalesOrderID							AS TransactionID,
		SalesOrderDetailID							AS TransactionDetailID,
		convert(date,DateID,23)						AS DateID,
		soh.TerritoryID								AS LocationID,
		ProductID,
		soh.CustomerID,
		UnitPrice,
		OrderQty,
		LineTotal,
		UnitPriceDiscount,
		(UnitPriceDiscount*UnitPrice*OrderQty)		AS TotalDiscount
INTO	dwaw.CustomerSales
FROM	AdventureWorks2017.Sales.SalesOrderDetail sod
		INNER JOIN AdventureWorks2017.Sales.SalesOrderHeader soh
			ON sod.SalesOrderID  = soh.SalesOrderID
		INNER JOIN dwaw.dimDate
			ON dwaw.dimDate.day  =  DAY(soh.OrderDate)
			AND dwaw.dimDate.month  =  MONTH(soh.OrderDate)
			AND dwaw.dimDate.year  =  YEAR(soh.OrderDate)
		INNER JOIN AdventureWorks2017.Sales.Customer
			ON soh.CustomerID  =  AdventureWorks2017.Sales.Customer.CustomerID
		INNER JOIN AdventureWorks2017.Person.Person
			ON AdventureWorks2017.Sales.Customer.PersonID  =  AdventureWorks2017.Person.Person.BusinessEntityID
WHERE	PersonType  =  'IN'


ALTER TABLE		dwaw.CustomerSales
	ADD PRIMARY KEY(TransactionDetailID);

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	TransactionDetailID
		INTEGER NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	TransactionID
		INTEGER NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	DateID DATE NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	CustomerID
		INTEGER NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	LocationID
		INTEGER NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ALTER COLUMN	ProductID
		INTEGER NOT NULL;

ALTER TABLE		dwaw.CustomerSales
	ADD FOREIGN KEY(DateID)
		REFERENCES dwaw.dimDate([date]);

ALTER TABLE		dwaw.CustomerSales
	ADD FOREIGN KEY	(CustomerID)
		REFERENCES	dwaw.DIMCustomer(CustomerID);

ALTER TABLE		dwaw.CustomerSales
	ADD FOREIGN KEY	(LocationID)
		REFERENCES	dwaw.DIMSalesLocation(LocationID);

ALTER TABLE		dwaw.CustomerSales
	ADD FOREIGN KEY	(ProductID)
		REFERENCES	DWAW.DIMProduct(ProductID);