SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active' --Active patients.
AND DOB <= DATEADD(YEAR, -12, GETDATE()) --Aged 12 or older.
AND InternalID IN 
	( --2 or more visits within the last 18 months.
	 SELECT INTERNALID
	 FROM VISITS
	 WHERE RECORDSTATUS = 1
	 AND VISITCODE != 12 --Not non visit.
	 AND VISITDATE >= DATEADD(MONTH, -18, GETDATE())
	 GROUP BY INTERNALID
	 HAVING COUNT(INTERNALID) > 1
	) 
AND InternalID NOT IN
	( -- any COVID vaccine administered
	 SELECT INTERNALID
	 FROM IMMUNISATIONS
	 WHERE RECORDSTATUS = 1
	 AND VACCINEID IN
		(
	 	 SELECT vac.VACCINEID
		 FROM BPSDrugs..VACCINES vac
		 INNER JOIN BPSDrugs..VACCINE_DISEASE vd ON vd.VACCINEID = vac.VACCINEID
		 WHERE vd.DISEASECODE = 34
		 AND vd.RecordStatus = 1
		)
	)
ORDER BY Surname, FirstName;

/*
All active Patients
Aged 12 and older.
Who have 2 or more visits within the last 18 months.
Who have NOT had any COVID vaccine administered.
*/