SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND ( -- Given 2 covid vaccines
	 SELECT COUNT(InternalID)
	 FROM IMMUNISATIONS
	 WHERE RECORDSTATUS = 1
	 AND INTERNALID = p.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
		) 
	) >= 2

AND InternalID NOT IN 
	(-- Not billed any other items then the covid suitability assessments
	 SELECT	InternalID
	 FROM INVOICES i INNER JOIN Services s ON i.INVOICEID = s.INVOICEID
	 WHERE i.RECORDSTATUS = 1
	 AND s.RECORDSTATUS = 1
	 AND s.MBSITEM NOT IN 
		(
		 93624, 93625, 93626, 93627, -- suitability to receive the first dose of a COVID-19 vaccine.
		 93634, 93635, 93636, 93637, -- suitability to receive the first dose of a COVID-19 vaccine - After Hours.
		 93644, 93645, 93646, 93647, -- suitability to receive the second or subsequent dose of a COVID-19 vaccine.
		 93653, 93654, 93655, 93656  --  suitability to receive the second or subsequent dose of a COVID-19 vaccine - After Hours.
		)
	 AND s.SERVICEDATE >= DATEADD(YEAR, -2, GETDATE()) -- Either remove or change -2 as required for number of years in the past no other billings have been made.
	)

AND InternalID IN 
	( -- Billed a second or subsequent dose of a COVID-19 vaccine.
     SELECT INTERNALID
     FROM INVOICES i
     INNER JOIN SERVICES s ON i.INVOICEID = s.INVOICEID
     WHERE i.RECORDSTATUS = 1
     AND s.RECORDSTATUS = 1
     AND s.MBSITEM IN 
		(
         93644, 93645, 93646, 93647, -- suitability to receive the second or subsequent dose of a COVID-19 vaccine
         93653, 93654, 93655, 93656 -- suitability to receive the second or subsequent dose of a COVID-19 vaccine - After Hours.
		)
	 Group By INTERNALID
	 HAVING COUNT(*) >= 2 -- Set this to 2 to account for second COVID shot and booster shot.
	)

AND InternalID NOT IN 
	(-- Has no future appointments.
	 SELECT	INTERNALID
	 FROM APPOINTMENTS
	 WHERE RECORDSTATUS = 1
	 AND APPOINTMENTDATE > CAST(GETDATE() AS DATE)
	)
ORDER BY Surname, Firstname;
--Patients who have had both Covid vaccinations and booster shot but not billed any other items.