My task sounds like this: “Select sales territory (name) with sales in May 2013 higher than the average monthly sales per sales territory (Use SalesTerritory, SalesHeader tables).” As I understand it, logically, I need to find what territory was the maximum average for May 2013, while I need to link two tables (the “name” field in the “salesterritory” table, the rest of the data in the second, but the “name” must be present).
I tried to divide the task into parts, and find at least a territory by id without a name, here is my code:
SELECT TerritoryID, MAX(avga.sal) from (select YEAR(OrderDate) AS 'Year', MONTH(OrderDate) AS 'Month', TerritoryID, AVG(TotalDue) AS 'sal' FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate), MONTH(OrderDate), TerritoryID having YEAR(OrderDate)=2013) as avga group by TerritoryID
This result does not appear to be correct even at this stage. Please help how to do it right? At least without the second table.