/* Which employees have sold the highest & lowest number of bikes to customers
in Washington State and Utah before March 9, 2008? */

--Pe.FirstName, SOD.OrderQty, StateP.StateProvinceCode

SELECT Pe.FirstName, MAX(SOD.OrderQty) AS max_order FROM [Person].[CountryRegion] CR
JOIN [Person].[StateProvince] StateP ON CR.CountryRegionCode = StateP.CountryRegionCode
JOIN [Sales].[SalesTerritory] ST ON StateP.TerritoryID = ST.TerritoryID
JOIN [Sales].[SalesOrderHeader] SOH ON ST.TerritoryID = SOH.TerritoryID
JOIN [Sales].[SalesOrderDetail] SOD ON SOH.SalesOrderID = SOD.SalesOrderID
JOIN [Sales].[SpecialOfferProduct] SOP ON SOD.ProductID = SOP.ProductID
JOIN [Production].[Product] Pr ON SOP.ProductID = Pr.ProductID
JOIN [Production].[ProductCategory] PC ON Pr.ProductSubcategoryID = PC.ProductCategoryID
JOIN [Sales].[SalesPerson] SalesP ON ST.TerritoryID = SalesP.TerritoryID
JOIN [Person].[Person] Pe ON SalesP.BusinessEntityID = PE.BusinessEntityID
WHERE PC.Name LIKE 'Bike%' AND StateP.StateProvinceCode IN ('WA', 'UT') AND SOH.ShipDate < '3/9/2008'
GROUP BY Pe.FirstName