SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND INTERNALID IN 
	(
	 SELECT INTERNALID
	 FROM
		(
		 SELECT asi.INTERNALID, asi.GIVENDATE
		 FROM IMMUNISATIONS asi
		 INNER JOIN
			(
			 SELECT Im.INTERNALID
			 FROM IMMUNISATIONS Im
			 WHERE im.VACCINEID IN ( 172 ) -- COVID 19 Vaccine AstraZeneca
			 AND im.RECORDSTATUS = 1
			 AND im.[SEQUENCE] = 1
			 AND im.NOTGIVENHERE = 0
			)i2 ON asi.INTERNALID = i2.INTERNALID
		 WHERE asi.RECORDSTATUS = 1
		 AND asi.NOTGIVENHERE = 0
		 AND asi.VACCINEID IN ( 172 ) -- COVID 19 Vaccine AstraZeneca
		 AND asi.[SEQUENCE] = 2
		 UNION
			(
			 SELECT pfi.INTERNALID, pfi.GIVENDATE
			 FROM IMMUNISATIONS pfi
			 INNER JOIN
				(
				 SELECT im.INTERNALID
				 FROM IMMUNISATIONS im
				 WHERE im.VACCINEID IN ( 170 ) -- Pfizer Comirnaty
				 AND im.RECORDSTATUS = 1
				 AND im.[SEQUENCE] = 1
				 AND im.NOTGIVENHERE = 0
				)
			 I2 ON pfi.INTERNALID = i2.INTERNALID
			 WHERE pfi.RECORDSTATUS = 1
			 AND pfi.NOTGIVENHERE = 0
			 AND pfi.VACCINEID IN ( 170 ) -- Pfizer Comirnaty
			 AND pfi.[SEQUENCE] = 2
			)
		 UNION
			(
			 SELECT mdr.INTERNALID, mdr.GIVENDATE
			 FROM IMMUNISATIONS mdr
			 INNER JOIN
				(
				 SELECT im.INTERNALID
				 FROM IMMUNISATIONS im
				 WHERE im.VACCINEID IN ( 174 ) -- Moderna Spikevax 
				 AND im.RECORDSTATUS = 1
				 AND im.[SEQUENCE] = 1
				 AND im.NOTGIVENHERE = 0
				)
			 I2 ON mdr.INTERNALID = i2.INTERNALID
			 WHERE mdr.RECORDSTATUS = 1
			 AND mdr.NOTGIVENHERE = 0
			 AND mdr.VACCINEID IN ( 174 ) -- Moderna Spikevax 
			 AND mdr.[SEQUENCE] = 2
			)
		 UNION
			(
			 SELECT jan.INTERNALID, jan.GIVENDATE
			 FROM IMMUNISATIONS jan
			 INNER JOIN
				(
				 SELECT im.INTERNALID
				 FROM IMMUNISATIONS Im
				 WHERE im.VACCINEID IN ( 175 ) -- Janssen-Cilag
				 AND im.RECORDSTATUS = 1
				 AND im.[SEQUENCE] = 1
				 AND im.NOTGIVENHERE = 0
				) I2 ON jan.INTERNALID = i2.INTERNALID
			 WHERE jan.RECORDSTATUS = 1
			 AND jan.NOTGIVENHERE = 0
			 AND jan.VACCINEID IN ( 175 ) -- Janssen-Cilag
			 AND jan.[SEQUENCE] = 2
			)
		) PIm
	 WHERE PIm.GIVENDATE
	 BETWEEN '20210101' AND '20210901' -- Enter date ranges here using yyyymmdd format eg. 1 july 2021 as 20210701
	)
ORDER BY Surname, Firstname;
-- The list of patients who have had BOTH vaccines with the practice (Second dose given within specified date range)