Link to home
Start Free TrialLog in
Avatar of ozzy t
ozzy t

asked on

Find Patients last appointment location

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 distinct
medicare_tbl.Patient_Number,
Patient_First_Name,
Patient_Last_Name,
Patient_DOB,
Usual_Dr_Name,
MAX(Service_Date_From) AS Last_Appointment

FROM 
(
	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_Number




GROUP BY 
medicare_tbl.Patient_Number,
Patient_First_Name,
Patient_Last_Name,
Patient_DOB,
Usual_Dr_Name

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The solution provided at #ID: https:#a42112312 is correct for the question asked. I am asking this solution to be accepted.

Thanks,

Mike
Thank you.