SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND INTERNALID IN
	(
	 SELECT INTERNALID
	 FROM
		(
		 SELECT i.INTERNALID, i.GIVENDATE
		 FROM IMMUNISATIONS i
		 WHERE i.RECORDSTATUS = 1
		 AND i.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 i.INTERNALID IN
			(-- include patients that have had 'X' doses 'Y' Months ago.
			 SELECT INTERNALID
			 FROM IMMUNISATIONS i2
			 WHERE RECORDSTATUS = 1
			 AND NOT EXISTS
				(
				 SELECT 1
				 FROM IMMUNISATIONS i3
				 WHERE i3.RECORDSTATUS = 1
				 AND i3.GIVENDATE >= DATEADD(Month, -4, GETDATE()) --No COVID Imm within last 'Y' Months.
				 AND i3.INTERNALID = i2.INTERNALID
				 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
					)
				)
			 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) = 3 -- 'X' Number of COVID Vaccines administered.
			)
		) PIm
	)
ORDER BY Surname, Firstname;

--All patients who had 3rd covid shot more than four months ago but have not had a fourth shot