--Assignment 12
--Darryl Read

--Question 1
declare @color varchar(50) = 'Blue'

if(@color is null OR @color = '')
select count(ProductID) as 'Amount of Products',
	   Round(avg(ListPrice),2) as 'Average',
	   min(ListPrice) as 'Minimum',
	   max(ListPrice) as 'Maximum'
from AdventureWorksLT.SalesLT.Product
else
select count(ProductID) as 'Order Quantity',
	   Round(avg(ListPrice),2) as 'Average',
	   min(ListPrice) as 'Minimum',
	   max(ListPrice) as 'Maximum',
	   Color
from AdventureWorksLT.SalesLT.Product
where Color = @color
group by Color



--Question 2
declare @companyName varchar(50) = 'Bulk Discount Store'

select	sum(OrderQty) as '# of Products',
		avg(UnitPrice) as 'Unit Price Avg',
		avg(UnitPriceDiscount) / avg(UnitPrice) * 100 as 'Avg Discount Price',
		sum(LineTotal) as 'Sum of all Products'
from AdventureWorksLT.SalesLT.Customer as C
	inner join AdventureWorksLT.SalesLT.SalesOrderHeader as SOH
	on C.CustomerID = SOH.CustomerID
	inner join AdventureWorksLT.SalesLT.SalesOrderDetail as SOD
	on SOH.SalesOrderID = SOD.SalesOrderID
where CompanyName = @companyName

--Question 3
declare @StartDate datetime = '03/15/2004'
declare @EndDate datetime = '01/01/2005'

select count(SalesOrderID),
	   avg(TotalDue),
	   max(DateDiff(day, OrderDate, ShipDate))
from AdventureWorksLT.SalesLT.SalesOrderHeader
where OrderDate > @StartDate AND OrderDate < @EndDate

--Question 4

select	CompanyName as 'Company Name',
		sum(OrderQty) as ' Number of products'
from AdventureWorksLT.SalesLT.Customer as C
	inner join AdventureWorksLT.SalesLT.SalesOrderHeader as SOH
	on C.CustomerID = SOH.CustomerID
	inner join AdventureWorksLT.SalesLT.SalesOrderDetail as SOD
	on SOH.SalesOrderID = SOD.SalesOrderID
group by CompanyName
order by sum(OrderQty) desc

--Question 5

select	PC.Name as 'Product Category',
		Avg(P.ListPrice) as 'Average Price',
		Max(P.ListPrice) as 'Highest Price',
		Min(P.ListPrice) as 'Lowest Price',
		Count(ProductID) as 'Number of Products'
from	AdventureWorksLT.SalesLT.ProductCategory as PC
		inner join AdventureWorksLT.SalesLT.Product as P
		on PC.ProductCategoryID = P.ProductCategoryID
group by PC.Name
order by Count(ProductID) desc