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!