title: Declaring variables in CTEs SQL Server 2008
body: <p>How can I implement something like the following:</p>
  
  <pre><code>declare @myInt int
  set @myInt=(select count(*) from x)
  ;with x as
  
  (
  select row_number() over(partition by c.patientid order by c.admissiondate) as rn
      ,c.patientid,c.admissiondate
      ,max(c.claimsfromdate)  as maxHemiDate
      ,min(c.claimsfromdate) minHemiDate  
      ,(
          select max(c2.claimsfromdate)
              from claims as c2
              where c2.patientid=c.patientid
              group by c2.patientid
       ) as maxClaimsDate
  
           ,p.drgCode
           ,datediff(dd,min(c.claimsfromdate),max(c.claimsfromdate)) /7 as weeksWithHemi
           from claims as c inner join icdclaims as ci on ci.id=c.id
           inner join tblicd as t on t.icd_id=ci.icd_id
           inner join patient as p on p.patientid=c.patientid
           and p.admissiondate = c.admissiondate
           and p.dischargedate = c.dischargedate
           where t.icdText like '%X%'  and p.statecode='21'
           group by c.patientid, c.admissiondate, p.drgCode
  )
  
  select p.patientid, count(*)
      from patient as p
      left join x on x.patientid=p.patientid
      where x.patientid is null
      group by p.patientid
  </code></pre>
  
  <p>The error thrown when this is executed is </p>
  
  <blockquote>
    <p><em>invalid object name x</em></p>
  </blockquote>
  
  <p>I kinda figured that this would happen since the variable declaration is outside of the CTE. If I move the declaration inside of the parentheses of <code>WITH</code> I get another error.  </p>
  
  <p>How can I assign a variable like this inside a CTE? Or can you not use a variable that draws data from the CTE at all?</p>