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.[sequence] = 2
		 --AND ASI.GIVENDATE <= DATEADD(Month, -5, '20211129') --change to the date you want to see eligible patients from in yyyymmdd format (e.g. setting date to 20211129 would show patients who received a sequence 2 dose May 29 2021 or earlier
		 AND ASI.GIVENDATE <= DATEADD(Month, -5, GETDATE()) --Remove quote '--' from the far left of this line to use 6 months and older from 'Todays Date'.
		) PIm
	)
--All Patients who have had 'sequence 2' COVID Vaccine 5 or more months ago.