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!
Try using a derived table instead.
SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG
FROM Patients p
JOIN (SELECT PatientID, AVG(BGValue) AvgBG FROM BloodGlucose group by PatientID ) BG
ON BG.PatientID = p.PatientID
WHERE AvgBG > 60;
The aliases in the WHERE clause can only come from the FROM clause. Here is a way to rewrite your query:
SELECT p.PatientID, p.PatientType, p.AccountNumber,
p.FirstName + ' ' + p.LastName as PatientFullName, p.CreatedDate,
DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
av.AvgBG
FROM Patients p join
(SELECT PatientId, AVG(BGValue) as AvgBG
FROM BloodGlucose
group by PatientID
) av
on p.PatientId = av.PatientId
WHERE av.AvgBG > 60;
SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG
FROM Patients p
outer apply (SELECT PatientID, AVG(BGValue) AvgBG
FROM BloodGlucose where PatientID = p.PatientID
group by PatientID ) BG
WHERE AvgBG > 60;
this should also work pretty quickly
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With