SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND DOB < DATEADD(Year, -50, GETDATE()) --Older than 50 years.
AND INTERNALID IN
	(
	 SELECT INTERNALID
	 FROM
		(
		 SELECT asi.INTERNALID,
		 asi.GIVENDATE
		 FROM IMMUNISATIONS asi
		 WHERE asi.RECORDSTATUS = 1
		 AND asi.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 asi.INTERNALID IN
			(
			 SELECT INTERNALID  -- include patients that have had at least 2 doses
			 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
				)
			 GROUP BY INTERNALID
			 HAVING COUNT(INTERNALID) = 2
			)
	       AND asi.GIVENDATE < DATEADD(Month, -4, GETDATE()) --Adjust number of months ago (Only change number).
		) PIm
	)
ORDER BY Surname, Firstname;

--Patients over 50 who had 2nd COVID shot more than 4 months ago, but have not had 3rd shot.