I am trying to determine the patients last appointment location, everything is fine until i add "location" field then i produce more results,since a patient more than likely has been treated in more than one location in area, i am stuck on how to go about this, how would i go about this? i am guessing i could try to tie last appointment date to location, i just don't know how. Thanks for any help or suggestions.
select distinctmedicare_tbl.Patient_Number,Patient_First_Name,Patient_Last_Name,Patient_DOB,Usual_Dr_Name,MAX(Service_Date_From) AS Last_AppointmentFROM ( SELECT distinct PM.vwGenPatInfo.Patient_Number,Patient_First_Name,Patient_Last_Name,Patient_DOB,Usual_Dr_Name,Service_Date_From,Location_Abbr FROM PM.vwGenPatInfo LEFT JOIN PM.vwGenSvcInfo ON PM.vwGenPatInfo.Patient_Number = PM.vwGenSvcInfo.Patient_Number WHERE (PM.vwGenPatInfo.Prim_Policy_Carrier_Name IN ('MEDICARE PART B', 'MEDICARE UGS')) AND PM.vwGenSvcInfo.Service_Date_From >= CONVERT(DATE, DATEADD(MONTH, -24, GETDATE()))) as medicare_tbl INNER JOIN( SELECT distinct PM.vwGenPatInfo.Patient_Number FROM PM.vwGenPatInfo LEFT JOIN PM.vwGenSvcInfo ON PM.vwGenPatInfo.Patient_Number = PM.vwGenSvcInfo.Patient_Number WHERE ( ( PM.vwGenPatInfo.PrimM_Policy_Carrier_Reporting_Class_Abbr in ('Table95a', 'Table95b', 'Table94') AND PM.vwGenPatInfo.SecM_Policy_Carrier_Reporting_Class_Abbr in ('Table92a', 'Table92b', 'Table91') ) OR ( PM.vwGenPatInfo.PrimM_Policy_Carrier_Reporting_Class_Abbr in ('Table92a', 'Table92b', 'Table91') AND PM.vwGenPatInfo.SecM_Policy_Carrier_Reporting_Class_Abbr in ('Table95a', 'Table95b', 'Table94') ) ) AND PM.vwGenSvcInfo.Service_Date_From >= CONVERT(DATE, DATEADD(MONTH, -24, GETDATE()))) as dually_eligible_tbl on medicare_tbl.Patient_Number = dually_eligible_tbl.Patient_NumberGROUP BY medicare_tbl.Patient_Number,Patient_First_Name,Patient_Last_Name,Patient_DOB,Usual_Dr_Name
Thanks,
Mike