title: Using subquery's alias in a WHERE statement
body: <p>I'm trying to use an alias created in a SELECT, but in a WHERE statement. I know it doesn't work and I just read why in another SO question.</p>
  
  <p>But my question is : what other solution should I take to make this work without repeating the subquery?</p>
  
  <pre><code>SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
  DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
  (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
  FROM Patients p
  WHERE AvgBG &gt; 60;
  </code></pre>
  
  <p>I know that this works:</p>
  
  <pre><code>SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
  DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
  (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
  FROM Patients p
  WHERE (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) &gt; 60;
  </code></pre>
  
  <p>But I don't want to repeat that subquery. And I'm pretty sure it isn't very performance-wise so that is the reason I'm asking for a better solution here.</p>
  
  <p>Thanks!</p>