Using SELECT result in another SELECT

Question

So here is my query

SELECT      *  FROM      Score  AS NewScores  WHERE      InsertedDate >= DATEADD(mm, -3, GETDATE());   SELECT      ROW_NUMBER() OVER( ORDER BY NETT) AS Rank,      Name,      FlagImg,      Nett,      Rounds  FROM (     SELECT          Members.FirstName + ' ' + Members.LastName AS Name,          CASE              WHEN MenuCountry.ImgURL IS NULL THEN                  '~/images/flags/ismygolf.png'              ELSE                  MenuCountry.ImgURL          END AS FlagImg,          AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett,          COUNT(Score.ScoreID) AS Rounds      FROM          Members          INNER JOIN          Score              ON Members.MemberID = Score.MemberID          LEFT OUTER JOIN MenuCountry              ON Members.Country = MenuCountry.ID      WHERE          Members.Status = 1      GROUP BY          Members.FirstName + ' ' + Members.LastName,          MenuCountry.ImgURL     ) AS Dertbl  ORDER BY;      

The query is to give a result set for a GridView based leaderboard and what I want is to only get the average of Scores that are less than 3 months old. I have this in 2 parts as you can see and obviously it gives an error like this.

Msg 4104, Level 16, State 1, Line 2

The multi-part identifier "NewScores.NetScore" could not be bound.

Which is because of this AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett

How do I make it so that I can use NewScores there so I'm only getting the average of the scores less than 3 months old?

EDIT: Using the answers people provided I've solved it by using a join in the correct place and here is the correct query:

SELECT ROW_NUMBER() OVER(ORDER BY NETT) AS Rank, Name, FlagImg, Nett, Rounds FROM (SELECT Members.FirstName + ' ' + Members.LastName AS Name, CASE WHEN MenuCountry.ImgURL IS NULL THEN '~/images/flags/ismygolf.png' ELSE MenuCountry.ImgURL END AS FlagImg, AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett, COUNT(NewScores.ScoreID) AS Rounds FROM Members INNER JOIN (SELECT * FROM Score WHERE InsertedDate >= DATEADD(mm, -5, GETDATE())) NewScores ON Members.MemberID = NewScores.MemberID LEFT OUTER JOIN MenuCountry ON Members.Country = MenuCountry.ID WHERE Members.Status = 1 GROUP BY Members.FirstName + ' ' + Members.LastName, MenuCountry.ImgURL) AS Dertbl ORDER BY Nett ASC 

Answer 1

NewScores is an alias to Scores table - it looks like you can combine the queries as follows:

SELECT      ROW_NUMBER() OVER( ORDER BY NETT) AS Rank,      Name,      FlagImg,      Nett,      Rounds  FROM (     SELECT          Members.FirstName + ' ' + Members.LastName AS Name,          CASE              WHEN MenuCountry.ImgURL IS NULL THEN                  '~/images/flags/ismygolf.png'              ELSE                  MenuCountry.ImgURL          END AS FlagImg,          AVG(CAST(NewScores.NetScore AS DECIMAL(18, 4))) AS Nett,          COUNT(Score.ScoreID) AS Rounds      FROM          Members          INNER JOIN          Score NewScores             ON Members.MemberID = NewScores.MemberID          LEFT OUTER JOIN MenuCountry              ON Members.Country = MenuCountry.ID      WHERE          Members.Status = 1          AND NewScores.InsertedDate >= DATEADD(mm, -3, GETDATE())     GROUP BY          Members.FirstName + ' ' + Members.LastName,          MenuCountry.ImgURL     ) AS Dertbl  ORDER BY;