SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active' -- Active patients.
AND DOB <= DATEADD(YEAR, -5, GETDATE()) -- Aged 5 or older.
AND DOB > DATEADD(YEAR, -12, GETDATE()) -- Aged 11 or younger.
AND InternalID NOT IN
	( -- Not been given a COVID Vaccine.
	 SELECT INTERNALID
	 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
		)
	)
ORDER BY Surname, FirstName;
-- All active Patients, Who have NOT had Astrazeneca, Moderna or Pfizer vaccine administered, aged 5-11.