SELECT		sub.*,
		ap.apat_code
FROM		k_apptreg AS ap
INNER JOIN	(
			SELECT		r.REFL_DATE,
					r.EVENT_NO,
					c.PREFERRED_NAME,
					c.SURNAME,
					r.ANNLAPPT_DATE,
					r.CLNP_CODE,
					p.SURNAME AS CLINICIAN, 
					r.DEPT_CODE,
					d.DEPT_TITLE,
					r.PT_CODE,
					r.REFLREAS_DESC,
					r.HOSP_CODE,
					r.REFP_CODE,
					MAX(a.APPT_DATE) AS LAST_APPT
			FROM		ORACARE.K_REFLREG AS r
			INNER JOIN	ORACARE.K_CPIREG AS c ON c.PT_CODE = r.PT_CODE
			INNER JOIN	ORACARE.K_DEPTLIST AS d ON d.DEPT_CODE = r.DEPT_CODE AND d.HOSP_CODE = r.HOSP_CODE
			INNER JOIN	ORACARE.K_PROFREG AS p ON p.MPROF_CODE = r.CONS_MD_CODE
			INNER JOIN	ORACARE.K_APPTREG AS a ON a.EVENT_NO = r.EVENT_NO AND a.XTEND_IND = 'Y'
			WHERE		r.ANNLAPPT_DATE < @PARAM1
			GROUP BY	r.REFL_DATE,
					r.EVENT_NO,
					c.PREFERRED_NAME,
					c.SURNAME,
					r.ANNLAPPT_DATE,
					r.CLNP_CODE,
					p.SURNAME,
					r.DEPT_CODE, 
					d.DEPT_TITLE,
					r.PT_CODE,
					r.REFLREAS_DESC,
					r.HOSP_CODE,
					r.REFP_CODE
		) AS sub ON sub.event_no = ap.event_no AND sub.last_appt = ap.appt_date AND xtend_ind = 'Y'
ORDER BY	sub.annlappt_date