SELECT *
FROM BPS_Patients p
WHERE StatusText = 'Active'
AND EXISTS 
	(
	 SELECT i2.INTERNALID, i2.VACCINEID, COUNT(1) AS countofrecords
	 FROM IMMUNISATIONS i2 
	 WHERE i2.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 i2.INTERNALID = p.InternalID
	 GROUP BY i2.INTERNALID, i2.VACCINEID
	 HAVING COUNT(1) = 2
	)
AND 
	( 
	 EXISTS 
		( 
		 SELECT 1 
		 FROM CURRENTRX crx 
		 WHERE crx.INTERNALID = p.InternalID 
		 AND crx.PRODUCTID IN 
			(
			 SELECT pa.PRODUCTID 
			 FROM BPSDrugs..PRODUCT_ATC pa 
			 WHERE pa.ATCCODE IN 
				(
				 'L04AA',
		 		 'L04AD',
		 		 'L04AX',
		 		 'L04AY', 
		 		 'L04AZ' 
				)
		 	)
		)	 
	 OR EXISTS 
		(
		 SELECT 1 
		 FROM PASTHISTORY ph 
		 WHERE ph.RECORDSTATUS = 1 
		 AND ph.INTERNALID = p.InternalID 
		 AND ph.ITEMTEXT IN 
			( 
			 'Acute granulocytic leukaemia'
			 ,'Acute lymphoblastic leukaemia'
			 ,'Acute lymphocytic leukaemia'
			 ,'Acute myelocytic leukaemia'
			 ,'Acute myeloid leukaemia'
			 ,'ALL'
			 ,'AML'
			 ,'Burkitt''s lymphoma'
			 ,'Chronic Granulocytic Leukaemia'
			 ,'Chronic Lymphatic Leukaemia'
			 ,'Chronic Lymphocytic Leukaemia'
			 ,'Chronic Myelocytic Leukaemia'
			 ,'Chronic Myeloid Leukaemia'
			 ,'CLL'
			 ,'CML'
			 ,'Cutaneous B-cell lymphoma'
			 ,'Cutaneous T-cell lymphoma'
			 ,'Eosinophilic leukaemia'
			 ,'Gastric lymphoma'
			 ,'Hairy cell leukaemia'
			 ,'Hodgkin''s disease'
			 ,'Hodgkin''s lymphoma'
			 ,'Hyper-eosinophilic syndrome'
			 ,'Leukaemia'
			 ,'Leukaemia, acute lymphoblastic'
			 ,'Leukaemia, acute lymphocytic'
			 ,'Leukaemia, acute myeloid'
			 ,'Leukaemia, chronic lymphocytic'
			 ,'Leukaemia, chronic myeloid'
			 ,'Leukaemia, hairy cell'
			 ,'Leukaemia, promyelocytic'
			 ,'Lymphoma'
			 ,'Lymphoma of small intestine'
			 ,'Lymphoma, Burkitt''s'
			 ,'Lymphoma, gastric'
			 ,'Lymphoma, Hodgkin''s'
			 ,'Lymphoma, Mantle cell'
			 ,'Lymphoma, Non Hodgkin''s'
			 ,'Lymphoma, small bowel'
			 ,'Lymphomatoid granulomatosis'
			 ,'Lymphomatoid papulosis'
			 ,'Lymphosarcoma'
			 ,'Mantle Cell Lymphoma'
			 ,'Multiple myeloma'
			 ,'Myelodysplasia'
			 ,'Myelo-dysplastic syndrome'
			 ,'Non Hodgkin''s lymphoma'
			 ,'Promyelocytic leukaemia'
			 ,'Small bowel lymphoma'
			 ,'T-cell lymphoma'
			)
		)
	)
ORDER BY Surname, Firstname;
-- Patients who have had exactly two COVID vaccine doses and have a haematological cancer or are prescribed an alkylating agent or calcineurin inhibitor