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

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Mike Eghtebas

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mike Eghtebas

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike Eghtebas

The solution provided at #ID: https:#a42112312 is correct for the question asked. I am asking this solution to be accepted.

Thanks,

Mike
Mike Eghtebas

Thank you.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck