Using subquery's alias in a WHERE statement

Viewed 14390

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.

But my question is : what other solution should I take to make this work without repeating the subquery?

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 > 60;

I know that this works:

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) > 60;

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.

Thanks!

6 Answers
Related