SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND 
	(2 = 
		(
		 SELECT count(1)
         FROM   IMMUNISATIONS i
         WHERE  i.INTERNALID = p.InternalID
         AND RECORDSTATUS = 1
         AND i.GIVENDATE < DATEADD(MONTH,-6,GETDATE())
         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
			)
		)
	)
AND 
	(2 = 
		(
		 SELECT COUNT(1)
         FROM IMMUNISATIONS i
         WHERE i.INTERNALID = p.InternalID
         AND 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;
--Patients eligible for a booster (3rd vaccination)that have had both COVID-19 vaccinations and their last dose was over 6 months ago