/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg & Yoder											*/
/*  Database Concepts (8th Edition) Chapter 08									*/
/*																				*/
/*	Marcia's Dry Cleaning Data Warehouse [MDC-DW] Case questions				*/
/*																				*/
/*	Microsoft SQL Server 2016 code solutions									*/
/*																				*/
/********************************************************************************/
/* run each individually (copy and paste to query window) */ 
/***  Chapter 8 - MDC-Problems   ************************************************/

SELECT * FROM INVOICE_ITEM;

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];


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];



