SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND 
	(
		(
		 p.DOB < DATEADD(Year, -50, GETDATE()) --Older than 50 years.
		 AND p.Ethnicity IN ('Aboriginal', 'Torres Strait Islander', 'Aboriginal/Torres Strait Islander')
		)
		OR 
		(
		 p.DOB < DATEADD(Year, -65, GETDATE()) --Older than 65 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 3 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) =3 
			)
		 AND asi.GIVENDATE < DATEADD(Month, -4, GETDATE()) --Adjust number of months ago (Only change number).
		 AND asi.GIVENDATE = 
			(
			 SELECT MAX(asi2.GIVENDATE) 
			 FROM IMMUNISATIONS asi2 
			 WHERE asi2.RECORDSTATUS = 1 
			 AND asi2.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
				)
			)
		) PIm
	)
ORDER BY Surname, Firstname;
--Patients over 65, or over 50 and Aboriginal or Torres Strait Islander who had 3rd covid shot more than four months ago but have not had a fourth shot