SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND -- Have only had one COVID vaccine
	(
	 SELECT COUNT(InternalID)
	 FROM IMMUNISATIONS
	 WHERE RECORDSTATUS = 1
	 AND InternalID = p.InternalID
	 AND GIVENDATE < CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
	 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
		) 
	) = 1
AND InternalID NOT IN -- Do not have a future COVID appointment booked
	(
	 SELECT InternalID
	 FROM APPOINTMENTS
	 WHERE RECORDSTATUS = 1
	 AND APPOINTMENTDATE > CAST(GETDATE() AS DATE)
	 AND APPOINTMENTTYPE IN 
		(
		 SELECT APPOINTMENTCODE
		 FROM APPOINTMENTTYPES
		 WHERE DESCRIPTION IN ('Immunisation', 'COVID') -- Enter appointment types here   
		)
	)
ORDER BY Surname, Firstname;
-- Patients who have been given the first vaccine and do not have an appointment booked for the second dose