SELECT *
FROM BPS_Patients p
WHERE 
	( -- Given dose 1
		(
		 SELECT	COUNT(InternalID)
		 FROM Immunisations
		 WHERE RecordStatus = 1
		 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
			)  
		) = 1
	 
	 AND InternalID NOT IN 
			( -- Not bulk billed 1st suitability assessment
			 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 
				(
				 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.
				)
			 GROUP BY InternalID
			 HAVING COUNT(*) >= 1
			)
	)
	 OR
	( -- Given dose 2
		(
		 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
		 	)
		) = 2
	
	AND InternalID NOT IN 
		( -- Not bulk billed 2nd suitability assessment
		 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(*) >= 1
		)
	)
	 OR
	( -- 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 bulk billed 2nd suitability assessment.
		 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
		)
	)
ORDER BY Surname, Firstname;
--Patients who have been vaccinated but haven't been billed the correct item(s).