1-

CREATE VIEW VItemCostHistory AS
SELECT	item.itemID,
		item.description,
		item.ListPrice,
		h1.LastCost,
		h1.LastCostDate
FROM		TblItem item
LEFT JOIN	tblitemCostHistory h1
ON		h1.itemID = item.itemID
WHERE	h1.LastCostDate = 	(
					SELECT	MAX(h2.LastCostDate)
					FROM	tblitemCostHistory h2
					WHERE h2.itemID = h1.itemID
					)

----------
CREATE VIEW VShippedQty AS
SELECT	ol.orderID "OrderID",
		ord.OrderDate,
		cust.LastName + ', '+ SUBSTRING(cust.firstname,1,1)+'.' "CustomerName",
		ol.itemID "ItemID",
		ol.quantity "QuantityOrdered",
		ISNULL(SUM(sl.QtyShipped), 0) "TotalShipped",
		ol.quantity - ISNULL(SUM(sl.QtyShipped), 0) "quantityremaining"
FROM		TblOrderLine ol
LEFT JOIN	TblOrder	ord
ON		ord.orderID = ol.OrderID
LEFT JOIN	TblShipLine sl
ON		sl.ItemID = ol.ItemID AND
		sl.OrderID = ol.OrderID
LEFT JOIN	TblCustomer cust
ON		ord.customerID = cust.customerID
GROUP BY	ol.orderID,
		cust.LastName,
		cust.FirstName,
		ol.ItemID,
		ord.OrderDate,
		ol.quantity
	
----------

SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID

****************************************
2-
SELECT	v.orderid "Order ID",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.TotalShipped-v.QuantityOrdered "Quantity Overshipped"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.TotalShipped > v.QuantityOrdered

****************************************
3-
SELECT	cust.lastname + ', ' + cust.firstName "Customer Name",
		ol.orderid "Order ID",
		CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
		ol.ItemID "Item ID",
		v.description "Item Name",
		ol.listprice "Price Paid",
		v.LastCost,
		CONVERT(VARCHAR, v.LastCostDate, 101) "Last Cost Date"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN VItemCostHistory v
ON		ol.itemID = v.itemID
WHERE	ol.listprice = (
		SELECT	MIN(ol2.listprice)
		FROM	TblOrderLine ol2
		LEFT JOIN TblItem item
		ON		ol2.itemID = item.itemID
		WHERE	item.description = 'Knowledge Management:  Create a Learning Organization'
		)

****************************************
4-
create view VOrderLine AS
SELECT	cust.lastname,
		cust.firstName,
		cust.customerID,
		ol.orderid,
		ord.OrderDate,
		ol.ItemID,
		ol.quantity "QuantityOrdered",
		ol.listprice "PricePaid",
		item.description,
		item.listprice "itemlistprice"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN tblItem item
ON		ol.itemID = item.itemID

-------

SELECT	v1.lastname + ', ' + v1.firstName "Customer Name",
		v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.itemlistprice "Item List Price",
		vhist.LastCost "Most Current Last Cost",
		CONVERT(VARCHAR, vhist.LastCostDate, 101) "Last Cost Date"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
WHERE	orderdate = (
		select MAX(orderdate)
		from VOrderLine v2
		where v2.description = 'Cranium WOW Edition'
		)
AND		v1.description = 'Cranium WOW Edition'

****************************************
5-
SELECT	v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.lastname "Customer Last Name",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.QuantityOrdered,
		v1.PricePaid,
		vhist.LastCost "Most Current Last Cost",
		v1.PricePaid - vhist.LastCost "pricediff",
		(v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered "extendeddiff"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID

-----

CREATE VIEW VTotalOrderProfit AS
SELECT	v1.orderid,
		SUM((v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered ) "TotalOrderProfit"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
GROUP BY v1.orderid

-------

SELECT	v1.orderID
FROM	Vtotalorderprofit v1
WHERE	v1.totalorderprofit = (
		SELECT	MAX(totalorderprofit)
		FROM	Vtotalorderprofit
		)

------

SELECT	v1.orderid "Order ID",
		CONVERT(VARCHAR, v1.orderdate, 101) "Order Date",
		v1.lastname "Customer Last Name",
		v1.itemID "Item ID",
		v1.description "Item Name",
		v1.QuantityOrdered,
		v1.PricePaid,
		vhist.LastCost "Most Current Last Cost",
		v1.PricePaid - vhist.LastCost "pricediff",
		(v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered "extendeddiff"
FROM	VOrderLine v1
LEFT JOIN VItemCostHistory vhist
ON		v1.itemID = vhist.itemID
WHERE	v1.orderID = (
		SELECT	v1.orderID
		FROM	Vtotalorderprofit v1
		WHERE	v1.totalorderprofit = (
				SELECT	MAX(totalorderprofit)
				FROM	Vtotalorderprofit
				)
		)

**********************************************
6-
CREATE VIEW VTotalItemQuantity AS
SELECT	cust.lastname,
		cust.firstName,
		cust.customerID,
		cust.phone,
		cust.state,
		ol.orderid,
		ord.OrderDate,
		SUM(ol.quantity) "TotalItemQuantity"
FROM	TblOrderLine ol
LEFT JOIN tblOrder ord
ON		ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON		cust.customerID = ord.customerID
LEFT JOIN tblItem item
ON		ol.itemID = item.itemID
GROUP BY	cust.lastname,
		cust.firstName,
		cust.customerID,
		cust.phone,
		cust.state,
		ol.orderid,
		ord.OrderDate
			
------
SELECT	lastname + ', ' + firstname "Customer Name",
		'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",
		UPPER(state) "State",
		TotalItemQuantity "Sum of Items Ordered"
FROM	VTotalItemQuantity
WHERE	totalitemquantity = (
			SELECT	MAX(totalitemquantity)
			FROM	VTotalItemQuantity
			)

***********************************************
7-
CREATE VIEW VOpenOrders AS
SELECT	DISTINCT(orderID)
FROM	VShippedQty
WHERE	quantityremaining > 0
----
SELECT	DISTINCT(orderID),
		orderdate
FROM	VShippedQty
WHERE	quantityremaining > 0

-------

SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.orderID IN (
				SELECT	OrderID
				FROM	VOpenOrders
				)


***************************************
8-
SELECT	v.orderid "Order ID",
		CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
		v.CustomerName "Customer Name",
		v.ItemID "Item ID",
		item.description "Item Name",
		v.QuantityOrdered "Quantity Ordered",
		v.TotalShipped "Total Shipped",
		v.quantityremaining "Quantity Left to Ship",
		CASE
			WHEN v.TotalShipped = 0
				THEN 'Not Shipped'
			WHEN v.QuantityOrdered = v.TotalShipped
				THEN 'Completed'
			WHEN v.TotalShipped > v.QuantityOrdered
				THEN 'Over Shipped'
			ELSE	 'Partially Shipped'
		END "Ship Status"
FROM		VShippedQty v
LEFT JOIN tblItem item
ON		v.itemID = item.itemID
WHERE	v.orderID IN (
				SELECT	OrderID
				FROM	VOpenOrders
				)
AND		v.OrderDate = (
				SELECT	MIN(OrderDate)
				FROM	VOpenOrders
				)

***************************************
9-
SELECT	v.orderID "Order ID",
		v.orderdate "Date of Order",
		v.customerID "Customer ID",
		v.lastname +', '+ UPPER(SUBSTRING(v.firstname,1,1)) "Customer Name",
		(SELECT	CONVERT(VARCHAR, MIN(dateshipped), 101)
		FROM	TblShipline t
		WHERE	t.orderID = v.orderID
		) "First Date Shipped",
		(SELECT	CONVERT(VARCHAR, MAX(dateshipped), 101)
		FROM	TblShipline t
		WHERE	t.orderID = v.orderID
		) "Last Date Shipped"
FROM	VTotalItemQuantity v
WHERE	orderID NOT IN (
		SELECT	orderID
		FROM	VOpenOrders
)
ORDER BY v.orderID

*****************************************
10-
insert into tblitemlocation values
('B67466','30',4)
insert into tblitemlocation values
('B67466','20',3)

SELECT	ItemID,
		SUM(QtyOnHand) "totalonhand"
FROM	TblItemLocation
GROUP BY ItemID

-----
CREATE VIEW Vitemtotalleft AS
SELECT	itemID,
		SUM(quantityremaining) "totalleft"
FROM	VShippedQty
GROUP BY itemID
HAVING	SUM(quantityremaining) > 0

-----

SELECT	TblItem.ItemID "Item ID",
		TblItem.description "Item Name",
		v.totalleft "Total Left to Ship",
		SUM(TblItemLocation.QtyOnHand) "Total On Hand",
		v.totalleft - SUM(TblItemLocation.QtyOnHand) "Total Short"
FROM	TblItemLocation 
LEFT JOIN TblItem
ON		TblItemLocation.itemID = tblitem.itemID
LEFT JOIN Vitemtotalleft v
ON		TblItemLocation.itemID = v.itemID
GROUP BY	TblItem.ItemID,
			TblItem.description,
			v.totalleft
HAVING	v.totalleft > SUM(TblItemLocation.QtyOnHand)

**********************************************
11-
SELECT	'CUSTOMER: '++ UPPER(lastname+', '+SUBSTRING(firstname,1,1)+'.') 
		"Customer or Item Not Purchased in January"
FROM	TblCustomer
WHERE	customerID NOT IN (
			SELECT	DISTINCT(customerID)
			FROM	VOrderLine
			WHERE	DATEPART(mm, OrderDate) = 1
			AND		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, GETDATE())
			)
UNION
SELECT	'ITEM: '+Description
FROM	TblItem
WHERE	itemID NOT IN (
			SELECT	DISTINCT(itemID)
			FROM	VOrderLine
			WHERE	DATEPART(mm, OrderDate) = 1
			AND		DATEPART(yyyy, OrderDate) = DATEPART(yyyy, GETDATE())
			)
ORDER BY 1



######################################################