I have this query that works however I have been told I need to add a field called Location. What they want to see is a list of all patients who have not been seen for a certain ApptCode since 07/21/2013. The problem I have with the location field is that it cannot be part of the aggregate group for the MAX function but they still want to see the location as part of the query. Using DM1 ApptCode as an example a patient could have the same code but be seen in different locations. Is there any way in a single pass to adjust this query to filter and group on the dates but still be able list the Location field as part of the query. The location field is part of Appointments.Location.
select t1.PatID,t1.PatName,Max(t1.LastApptDate) as LastApptDate,t1.ApptCode,t1.PatDOB ,t1.Location
from Appointments t1
join Patients t2 on t1.PatID=t2.PatID
where t1.ApptCode in ('RETEX','DM1','RET1','MD1')
group by t1.Account,t1.PatName,t2.HomePhone,t2.MobilePhone,t2.WorkPhone,t1.ApptCode,t1.PatDOB
WHERE MAX(t1.LastApptDate) <= '20130721'
order by t1.PatID
In this case, Elmer should not be included in the list because his Dallas appointment occurred after 07/21/2013 but Bugs Bunny would be included in the list because both of his appointments are before 07/21/2013. If I were to add Location to the Group By then the Elmer visit in Chicago would be included since the date is prior to 07/21/2013.