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.NOTGIVENHERE = 0
		 AND VACCINEID IN 
			(
			 172 -- COVID 19 Vaccine AstraZeneca
			)
		)
	OR 2 <= 
		(
		 SELECT COUNT(1)
		 FROM IMMUNISATIONS i
		 WHERE i.INTERNALID = p.InternalID
		 AND RECORDSTATUS = 1
		 AND i.NOTGIVENHERE = 0
		 AND VACCINEID IN 
			( 
			 170 -- Pfizer Comirnaty
			)
		)

	OR 2 <= 
		(
		 SELECT COUNT(1)
		 FROM IMMUNISATIONS i
		 WHERE i.INTERNALID = p.InternalID
		 AND RECORDSTATUS = 1
		 AND i.NOTGIVENHERE = 0
		 AND VACCINEID IN 
			(
			 174 -- Moderna Spikevax 
			)
		)
	OR 2 <= 
		(
		 SELECT COUNT(1)
		 FROM IMMUNISATIONS i
		 WHERE i.INTERNALID = p.InternalID
		 AND RECORDSTATUS = 1
		 AND i.NOTGIVENHERE = 0
		 AND VACCINEID IN 
			( 
			 175 -- Janssen-Cilag
			)
		)
	)
ORDER BY Surname, Firstname;
-- The list of patients who have had BOTH vaccines with the practice
