troubleshooting Question

Find Patients last appointment location

Avatar of ozzy t
ozzy t asked on
SQLMicrosoft SQL Server
3 Comments1 Solution97 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Mike Eghtebas
Database and Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros