SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID IN
	(
	 SELECT InternalID  -- include patients that have had at least 2 doses
	 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
	 	)
	 GROUP BY InternalID
	 HAVING COUNT(InternalID) <= 2
	)
ORDER BY Surname, Firstname;
--Patients who have had two or less doses of a COVID vaccine