SELECT *
FROM BPS_Patients p
WHERE 
	( -- Given booster dose.
		(
		 SELECT	COUNT(InternalID)
		 FROM Immunisations
		 WHERE RecordStatus = 1
		 AND InternalID = p.InternalID
		 AND GivenDate < CAST(GETDATE() AS DATE)
		 AND VaccineID IN 
			(
		 	 170, -- Pfizer Comirnaty 
		 	 172, -- COVID 19 Vaccine AstraZeneca 
		 	 174, -- Moderna Spikevax
		 	 175, -- Janssen-Cilag
			 176, -- AstraZeneca COVISHIELD
			 181  -- Novavax NUVAXOVID
		 	)
		) >= 3
	
	AND InternalID NOT IN 
		( -- Not billed Incentive item number
		 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 = 00000  -- Enter incentive item number here		
		 GROUP BY InternalID
		)
	)
ORDER BY Surname, Firstname;
--Patients who have been given the booster but haven't been billed the correct item(s).