mauza Mar 20th, 2017 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- 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
- SUM((OL.Quantity * OL.UnitPrice) + ((OL.Quantity * OL.UnitPrice)*OL.TaxRate/100)) [TotalSales]
- 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
- YEAR(O.OrderDate) = 2013
- GROUP BY
- ORDER BY
- TotalSales DESC
RAW Paste Data