SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND InternalID NOT IN
	(      
	 SELECT v.InternalID
	 FROM VISITS v
	 LEFT JOIN IMMUNISATIONS AS i
	 ON v.VisitID = i.VisitID
	 WHERE 
		(
		 i.VACCINEID NOT 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
			)
		OR i.VACCINEID IS NULL
		)
	AND v.RECORDSTATUS = 1
	)
AND InternalID IN
	(
	 SELECT INTERNALID
	 FROM  IMMUNISATIONS
	 WHERE 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 RECORDSTATUS = 1
	)
AND InternalID NOT IN
	(      
	 SELECT INTERNALID
	 FROM APPOINTMENTS a
	 WHERE RECORDSTATUS = 1
	 AND CAST(DATEADD(SS, a.AppointmentTime, a.AppointmentDate) AS DATETIME) >= getdate()
	)
ORDER BY Surname, Firstname;
-- Patient who have only visited to receive the COVID vaccine, with no future appointments.