/****************************************************************************************/
/*																						*/
/*	Kroenke, Auer, Vandenberg, and Yoder - Database Concepts (8th Edition) Chapter 08	*/
/*																						*/
/*	Heather Sweeny Designs Data Warehouse Database Exercise		8.52.N						*/
/*																						*/
/*	These are the Microsoft SQL Server 2016 code solutions								*/
/*																						*/
/****************************************************************************************/

/* ******** Exercise 8.52.N View Defintion  *********************************/

CREATE VIEW HSDDWProductDollarSalesPaymentTypeView AS
	SELECT		C.CustomerID, C.CustomerName, C.City,
				PT.PaymentType,
				P.ProductNumber, P.ProductName,
				SUM(PS.Total) AS TotalDollarSales
	FROM		CUSTOMER C, PRODUCT_SALES PS,
				PRODUCT P, PAYMENT_TYPE PT
	WHERE		C.CustomerID = PS.CustomerID
		AND		P.ProductNumber = PS.ProductNumber
		AND		PT.PaymentTypeID = PS.PaymentTypeID
	GROUP BY	C.CustomerID, C.CustomerName, C.City,
				P.ProductNumber, P.ProductName, PT.PaymentType;



				
/* ******** Exercise 8.52.N run SELECT View *************************************/
/* cut and paste into a query window to run this
SELECT * FROM HSDDWProductDollarSalesPaymentTypeView;
*/