quote:

---

Originally posted by rv498

- List the name, title, and hire date for AdventureWorks employees
-- whose sales quota for 2008 is higher than the average for all employees for 2008.
-- Be sure to use an appropriate sort.
-- 16 Rows

Error I get is CalendarYear is an invalid column. Well it belongs to C2 table but where in the code I need to declare it in order for computer to recognize it?


SELECT C1.LastName+', '+ C1.FirstName AS FullName, C1.Title, C1.HireDate
FROM dbo.DimEmployee AS C1
JOIN (SELECT AVG(SalesAmountQuota) AS AvgQuota FROM dbo.FactSalesQuota where CalendarYear = '2008') AS C2
ON C2.CalendarYear = '2008' AND C2C1.SalesAmountQuota > C2.AvgQuota
where C1.CalendarYear = '2008'

---

SELECT C1.LastName+', '+C1.FirstName AS FullName
      ,C1.Title
      ,C1.HireDate
  FROM dbo.DimEmployee AS C1
       INNER JOIN dbo.FactSalesQuota AS F
               ON F.EmployeeKey=C1.EmployeeKey
       INNER JOIN (SELECT F.CalendarYear
                         ,AVG(F.SalesAmountQuota) AS AvgQuota
                     FROM dbo.FactSalesQuota AS F
                    WHERE F.CalendarYear='2008'
                    GROUP BY F.CalendarYear
                  ) AS C2
               ON C2.CalendarYear=F.CalendarYear
              AND C2.AvgQuota<F.SalesAmountQuota