SHARE
TWEET

Untitled

mauza Mar 20th, 2017 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. Question #2 - 10 points
  3.  
  4. Write a query to return sales totals for each sales territory for the year
  5.     or 2013.  
  6.  
  7. Return two columns - the Sales Territory name and the total sales.  Total
  8.     sales is calculated by multiplying the unit price by quantity, then adding
  9.     in the tax amount for each line of the invoice.  Sort the list from largest
  10.     to smallest total sales.
  11.  
  12. Sales Territory can be found on the StateProvince table.  Use the DeliveryCityID
  13.     on the Customer table to join into Cities.
  14.  
  15. You will get 9 rows.  The first row should be "Southeast" with a total sales of 11,946,896.04
  16.  
  17.  
  18. */
  19.  
  20. SELECT
  21.     SP.SalesTerritory,
  22.     SUM((OL.Quantity * OL.UnitPrice) + ((OL.Quantity * OL.UnitPrice)*OL.TaxRate/100)) [TotalSales]
  23.  
  24. FROM
  25.     Sales.Orders O
  26.     INNER JOIN Sales.OrderLines OL ON O.OrderID = OL.OrderID
  27.     INNER JOIN Sales.Customers Cus ON Cus.CustomerID = O.CustomerID
  28.     INNER JOIN Application.Cities Cit ON Cus.DeliveryCityID = Cit.CityID
  29.     INNER JOIN Application.StateProvinces SP ON Cit.StateProvinceID = SP.StateProvinceID
  30.  
  31. WHERE
  32.     YEAR(O.OrderDate) = 2013
  33.  
  34. GROUP BY
  35.     SP.SalesTerritory
  36.  
  37. ORDER BY
  38.     TotalSales DESC
RAW Paste Data
Top