SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND 
	(
		( -- Have only had one COVID vaccine
		 SELECT COUNT(InternalID)
		 FROM IMMUNISATIONS i
		 WHERE RECORDSTATUS = 1
		 AND InternalID = p.InternalID
		 AND VACCINEID = 170 -- Pfizer Comirnaty 
		 AND i.INTERNALID = p.InternalID
		) = 1
	 OR
		(
		 SELECT COUNT(InternalID)
		 FROM IMMUNISATIONS i
		 WHERE RECORDSTATUS = 1
		 AND InternalID = p.InternalID
		 AND VACCINEID = 172 -- COVID 19 Vaccine AstraZeneca
         AND i.INTERNALID = p.InternalID
		) = 1
	 OR
		(
		 SELECT COUNT(InternalID)
		 FROM IMMUNISATIONS i
		 WHERE RECORDSTATUS = 1
		 AND InternalID = p.InternalID
		 AND VACCINEID = 174 -- Moderna Spikevax
		 AND i.INTERNALID = p.InternalID
		) = 1
	 OR
		(
		 SELECT COUNT(InternalID)
		 FROM IMMUNISATIONS i
		 WHERE RECORDSTATUS = 1
		 AND InternalID = p.InternalID
		 AND VACCINEID = 175 -- Janssen-Cilag
		 AND i.INTERNALID = p.InternalID
		) = 1
	)
ORDER BY Surname, Firstname;
-- The list of patients who have had their first dose with the practice, but have not had their second dose