SELECT *
FROM BPS_Patients p
WHERE statustext = 'Active'
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
	)
--Patients who had 2nd COVID shot more than 4 months ago, but have not had 3rd shot.