SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND EXISTS 
	(
	 SELECT	i2.INTERNALID, i2.VACCINEID, COUNT(1) AS countofrecords
	 FROM IMMUNISATIONS i2 
	 WHERE i2.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 i2.INTERNALID = p.InternalID
	 GROUP BY i2.INTERNALID,  i2.VACCINEID
	 HAVING COUNT(1) = 2
	)
AND EXISTS 
	(
	 SELECT 1 
	 FROM PASTHISTORY ph 
	 WHERE ph.RECORDSTATUS = 1 
	 AND ph.INTERNALID = p.InternalID 
	 AND ph.ITEMCODE IN 
		( 
		 SELECT TERMID 
		 FROM BPSDrugs..TERM_TERMGROUPS 
		 WHERE TERMGROUPID = 7066
		)
	)
ORDER BY Surname, Firstname;
--Patients who have had exactly two COVID vaccine doses and have a cancer recorded in their past history