/*
Question #2 - 10 points

Write a query to return sales totals for each sales territory for the year
    or 2013.

Return two columns - the Sales Territory name and the total sales.  Total
    sales is calculated by multiplying the unit price by quantity, then adding
    in the tax amount for each line of the invoice.  Sort the list from largest
    to smallest total sales.

Sales Territory can be found on the StateProvince table.  Use the DeliveryCityID
    on the Customer table to join into Cities.

You will get 9 rows.  The first row should be "Southeast" with a total sales of 11,946,896.04


*/

SELECT
    SP.SalesTerritory,
    SUM((OL.Quantity * OL.UnitPrice) + ((OL.Quantity * OL.UnitPrice)*OL.TaxRate/100)) [TotalSales]

FROM
    Sales.Orders O
    INNER JOIN Sales.OrderLines OL ON O.OrderID = OL.OrderID
    INNER JOIN Sales.Customers Cus ON Cus.CustomerID = O.CustomerID
    INNER JOIN Application.Cities Cit ON Cus.DeliveryCityID = Cit.CityID
    INNER JOIN Application.StateProvinces SP ON Cit.StateProvinceID = SP.StateProvinceID

WHERE
    YEAR(O.OrderDate) = 2013

GROUP BY
    SP.SalesTerritory

ORDER BY
    TotalSales DESC