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) > 1
	)
AND InternalID NOT IN
	(
	 SELECT InternalID   -- do not include patients that have had a dose in the last 6 months
	 FROM   IMMUNISATIONS
	 WHERE  RECORDSTATUS = 1
	 AND DATEDIFF(MONTH, GIVENDATE, GETDATE()) < 6
	 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;

--Patients who have had at least 2 doses and the last dose was over 6 months ago