title: Access alias in subquery
body: <p>I need to access an alias from the outer query in the subquery, like this code. Except it is not working. Is there a way to do this?
  My goal is to make these aggregate functions for every OwnerIdName and this is the only way I can think of.
  I am working in SQL Server 2008.</p>
  
  <p>Thanks</p>
  
  <pre><code>SELECT Incident.OwnerIdName as OIN
    ,(SELECT COUNT(*)
      FROM Incident 
      WHERE CreatedOn BETWEEN '2011/1/1' AND '2011/1/31'
      AND Incident.OwnerIdName = OIN ) as CasesOpened
    ,(SELECT COUNT(*)
      FROM IncidentResolution 
      WHERE ActualEnd BETWEEN '2011/1/1' AND '2011/1/31'
              AND Incident.OwnerIdName = OIN ) as CasesClosed
  FROM Incident
  </code></pre>