title: CTE Invalid column name
body: <p>I am completely stuck on this one.  I looked in the other questions, but could not find one that answered this (that I could understand, anyway).  I have the following CTE in my query but MaxUserID is squiggled red in the 3 places it's used with an "invalid column name 'MaxUserID'" error.  The column it should represent is an int, if that helps.   Any advice?</p>
  
  <p>I'm using SQL Server 2008.</p>
  
  <pre><code>;with TotalCount(TotalCount,MaxUserID)
  
  as
  
  (
      Select ISNULL(count(distinct uCPR.HeaderID), 0) as TotalCount, MaxUserID
              from ClientFeedback.dbo.UnitCountCPR uCPR
              where 
                  uCPR.DHDate between @StartDate and @EndDateMod
                  and uCPR.TargetID in (@StatusID)
                  and uCPR.UserID = MaxUserID
                  and uCPR.DTStamp between @StartDate and @EndDateMod
                  and uCPR.ClientID in (@ClientID)
        group by MaxUserID
  
  )
  </code></pre>