/****************************************************************************************/
/*																						*/
/*	Kroenke, Auer, Vandenberg, and Yoder - Database Concepts (8th Edition) Chapter 08	*/
/*																						*/
/*	Heather Sweeney Designs Data Warehouse Database Load Tables							*/
/*																						*/
/*	These are the Microsoft SQL Server 2016 code solutions								*/
/*																						*/
/****************************************************************************************/

/* Update PRODUCT_SALES table with PAYMENT-TYPE data from INVOICE and LINE_ITEM tables */
/*****   Invoice 35000   **************************************************/
/*
INSERT INTO INVOICE VALUES(
	'15-Oct-2016', 3, 'VISA', 22.94, 5.95, 1.31, 30.20);
INSERT INTO LINE_ITEM VALUES(35000, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35000, 2, 'VB001', 1, 7.99, 7.99);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42658
		AND	CustomerID = 3
		AND	ProductNumber = 'VB001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42658
		AND	CustomerID = 3
		AND		ProductNumber = 'VK001';

/*****   Invoice 35001   **************************************************/
/*
INSERT INTO INVOICE VALUES(
	'25-Oct-2016', 4, 'MasterCard', 47.89, 5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35001, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35001, 2, 'VB001', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35001, 3, 'BK001', 1, 24.95, 24.95);
*/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42668
		AND	CustomerID = 4
		AND	ProductNumber = 'VK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42668
		AND	CustomerID = 4
		AND		ProductNumber = 'VB001';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42668
		AND	CustomerID = 4
		AND	ProductNumber = 'BK001';

/*****   Invoice 35002   **************************************************/
/*
INSERT INTO INVOICE VALUES(
	'20-Dec-2016', 7, 'VISA', 24.95, 5.95, 1.42, 32.32);
INSERT INTO LINE_ITEM VALUES(35002, 1, 'VK004', 1, 24.95, 24.95);
*/

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42724
		AND	CustomerID = 7
		AND		ProductNumber = 'VK004';

/*****   Invoice 35003   **************************************************/
/* 
INSERT INTO INVOICE VALUES(
	'25-Mar-2017', 4, 'MasterCard', 64.85, 5.95, 3.70, 74.50);
INSERT INTO LINE_ITEM VALUES(35003, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35003, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35003, 3, 'VK004', 1, 24.95, 24.95);
*/
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42819
		AND	CustomerID = 4
		AND	ProductNumber = 'VK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42819
		AND	CustomerID = 4
		AND		ProductNumber = 'BK002';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42819
		AND	CustomerID = 4
		AND	ProductNumber = 'VK004';

/*****   Invoice 35004   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'27-Mar-2017', 6, 'MasterCard', 94.79, 5.95, 5.40, 106.14);
INSERT INTO LINE_ITEM VALUES(35004, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35004, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35004, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35004, 4, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35004, 5, 'VK004', 1, 24.95, 24.95);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 6
		AND		ProductNumber = 'VK002';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 6
		AND	ProductNumber = 'BK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 6
		AND		ProductNumber = 'VK003';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 6
		AND	ProductNumber = 'VB003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 6
		AND		ProductNumber = 'VK004';

/*****   Invoice 35005   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'27-Mar-2017', 7, 'MasterCard', 94.80, 5.95, 5.40, 106.15);
INSERT INTO LINE_ITEM VALUES(35005, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35005, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35005, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35005, 4, 'VK004', 1, 24.95, 24.95);
*/ 
 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 7
		AND	ProductNumber = 'BK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 7
		AND		ProductNumber = 'BK002';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 7
		AND	ProductNumber = 'VK003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42821
		AND	CustomerID = 7
		AND		ProductNumber = 'VK004';

/*****   Invoice 35006   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'31-Mar-2017', 9, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35006, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35006, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35006, 3, 'VB001', 1, 7.99, 7.99);
*/

 UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42825
		AND	CustomerID = 9
		AND	ProductNumber = 'BK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42825
		AND	CustomerID = 9
		AND		ProductNumber = 'VK001';

 UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42825
		AND	CustomerID = 9
		AND	ProductNumber = 'VB001';

/*****   Invoice 35007   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'03-Apr-2017', 11, 'MasterCard', 109.78, 5.95, 6.26, 121.99);
INSERT INTO LINE_ITEM VALUES(35007, 1, 'VK003', 2, 19.95, 39.90);
INSERT INTO LINE_ITEM VALUES(35007, 2, 'VB003', 2, 9.99, 19.98);
INSERT INTO LINE_ITEM VALUES(35007, 3, 'VK004', 2, 24.95, 49.90);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42828
		AND	CustomerID = 11
		AND		ProductNumber = 'VK003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42828
		AND	CustomerID = 11
		AND		ProductNumber = 'VB003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42828
		AND	CustomerID = 11
		AND		ProductNumber = 'VK004';

/*****   Invoice 35008   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'08-Apr-2017', 5, 'MasterCard', 47.89, 5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35008, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35008, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35008, 3, 'VB001', 1, 7.99, 7.99);
*/

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42833
		AND	CustomerID = 5
		AND		ProductNumber = 'BK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42833
		AND	CustomerID = 5
		AND		ProductNumber = 'VK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42833
		AND	CustomerID = 5
		AND		ProductNumber = 'VB001';

/*****   Invoice 35009   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'08-Apr-2017', 1, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35009, 1, 'BK001', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35009, 2, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35009, 3, 'VB001', 1, 7.99, 7.99);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42833
		AND	CustomerID = 1
		AND		ProductNumber = 'BK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42833
		AND	CustomerID = 1
		AND		ProductNumber = 'VK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42833
		AND	CustomerID = 1
		AND		ProductNumber = 'VB001';

/*****   Invoice 35010   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'23-Apr-2017', 3, 'VISA', 24.95,	5.95, 1.42, 32.32);
INSERT INTO LINE_ITEM VALUES(35010, 1, 'BK001', 1, 24.95, 24.95);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42848
		AND	CustomerID = 3
		AND		ProductNumber = 'BK001';

/*****   Invoice 35011   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'07-May-2017', 9, 'VISA', 22.94,	5.95, 1.31, 30.20);
INSERT INTO LINE_ITEM VALUES(35011, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35011, 2, 'VB002', 1, 7.99, 7.99);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42862
		AND	CustomerID = 9
		AND		ProductNumber = 'VK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42862
		AND	CustomerID = 9
		AND		ProductNumber = 'VB002';

/*****   Invoice 35012   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'21-May-2017', 8, 'MasterCard', 54.89, 5.95, 3.13, 63.97);
INSERT INTO LINE_ITEM VALUES(35012, 1, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35012, 2, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35012, 3, 'VK004', 1, 24.95, 24.95);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42876
		AND	CustomerID = 8
		AND		ProductNumber = 'VK003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42876
		AND	CustomerID = 8
		AND		ProductNumber = 'VB003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42876
		AND	CustomerID = 8
		AND		ProductNumber = 'VK004';

/*****   Invoice 35013 and 35016 processed together, merge two sales of VK002 and VB002 for */
/* Same customer 3 and same Date 42891, thus we have 48 rows in PRODUCT_SALES and 50 LINE_ITEMS *******/
/* we have 7 line items, and 5 inserts since we skip the duplicates. */ 
/* The Quantity is correct in the Product_Sales table */ 
/*****   Invoice 35013   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'05-Jun-2017', 3, 'VISA', 47.89,	5.95, 2.73, 56.57);
INSERT INTO LINE_ITEM VALUES(35013, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35013, 2, 'VB002', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35013, 3, 'BK002', 1, 24.95, 24.95);
*/
/*****   Invoice 35016   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'05-Jun-2017', 3, 'VISA', 45.88,	5.95, 2.62, 54.45);
INSERT INTO LINE_ITEM VALUES(35016, 1, 'VK001', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35016, 2, 'VB001', 1, 7.99, 7.99);
INSERT INTO LINE_ITEM VALUES(35016, 3, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35016, 4, 'VB002', 1, 7.99, 7.99);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 3
		AND		ProductNumber = 'VK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 3
		AND		ProductNumber = 'VB002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 3
		AND		ProductNumber = 'BK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 3
		AND		ProductNumber = 'VK001';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 3
		AND		ProductNumber = 'VB001';

/*****   Invoice 35014   ***************************************************/
/* 
INSERT INTO INVOICE VALUES(
	'05-Jun-2017', 11, 'MasterCard', 45.88, 5.95, 2.62, 54.45);
INSERT INTO LINE_ITEM VALUES(35014, 1, 'VK002', 2, 14.95, 29.90);
INSERT INTO LINE_ITEM VALUES(35014, 2, 'VB002', 2, 7.99, 15.98);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 11
		AND		ProductNumber = 'VK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 1
	WHERE		TimeID = 42891
		AND	CustomerID = 11
		AND		ProductNumber = 'VB002';


/*****   Invoice 35015   ***************************************************/
/*
INSERT INTO INVOICE VALUES(
	'05-Jun-2017', 12, 'MasterCard', 94.79, 5.95, 5.40, 106.14);
INSERT INTO LINE_ITEM VALUES(35015, 1, 'VK002', 1, 14.95, 14.95);
INSERT INTO LINE_ITEM VALUES(35015, 2, 'BK002', 1, 24.95, 24.95);
INSERT INTO LINE_ITEM VALUES(35015, 3, 'VK003', 1, 19.95, 19.95);
INSERT INTO LINE_ITEM VALUES(35015, 4, 'VB003', 1, 9.99, 9.99);
INSERT INTO LINE_ITEM VALUES(35015, 5, 'VK004', 1, 24.95, 24.95);
*/
UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42891
		AND	CustomerID = 12
		AND		ProductNumber = 'VK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42891
		AND	CustomerID = 12
		AND		ProductNumber = 'BK002';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42891
		AND	CustomerID = 12
		AND		ProductNumber = 'VK003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42891
		AND	CustomerID = 12
		AND		ProductNumber = 'VB003';

UPDATE PRODUCT_SALES SET PaymentTypeID = 2
	WHERE		TimeID = 42891
		AND	CustomerID = 12
		AND		ProductNumber = 'VK004';