/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg & Yoder											*/
/*  Database Concepts (8th Edition) Chapter 08									*/
/*																				*/
/*	Marcia's Dry Cleaning Data Warehouse [MDC-DW] Case questions				*/
/*																				*/
/*	MySQl 5.7 code solutions													*/
/*																				*/
/********************************************************************************/
/* run each individually  */ 
/***  Chapter 8 - MDC-Problems   ************************************************/
/* E - see DBC-e08-MySQL-MDC-Create-Tables.sql  */
/* G - see DBC-e08-MySQL-MDC-Insert-Data.sql  */

SELECT * FROM INVOICE_ITEM;
/* I  */
SELECT		S.ServiceID, S.ServiceDescription,
			T.MonthText, T.QuarterText, T.Year,
			SUM(II.ExtendedPrice) AS TotalGrossIncome
FROM		INVOICE_ITEM II, SERVICE S, TIMELINE T
WHERE		II.ServiceID = S.ServiceID
	AND		II.TimeID = T.TimeID
GROUP BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year
ORDER BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year;

/* J */
CREATE VIEW MDCDWServiceTotalGrossIncomeView AS
	SELECT		S.ServiceID, S.ServiceDescription,
				T.MonthText, T.QuarterText, T.Year,
				SUM(II.ExtendedPrice) AS TotalGrossIncome
	FROM		INVOICE_ITEM II, SERVICE S, TIMELINE T
	WHERE		II.ServiceID = S.ServiceID
		AND		II.TimeID = T.TimeID
	GROUP BY	S.ServiceID, S.ServiceDescription, T.MonthText, T.QuarterText, T.Year;

SELECT		*
FROM		MDCDWServiceTotalGrossIncomeView
ORDER BY	ServiceID, ServiceDescription, MonthText, QuarterText, Year;



