SELECT *
FROM BPS_Patients
WHERE StatusText = 'Active'
AND  DOB < DATEADD(YEAR, -50, GETDATE()) -- Patient is 50 years and older
AND  
	(-- 70 years or older
	 DOB < DATEADD(YEAR, -70, GETDATE())
	 OR 
		(-- Abororiginal or Torres Strait Islander and 55 years or older
		 DOB < DATEADD(YEAR, -55, GETDATE()) 
		 AND Ethnicity IN 
			(
			 'Aboriginal/Torres Strait Islander'
			 ,'Aboriginal'
			 ,'Torres Strait Islander'
			)
		)
	 OR	
		( -- Healthcare, Aged Care, Disability and Critical and High Risk worker
		 InternalID IN 
			(
			 SELECT INTERNALID 
			 FROM OCCUPATIONHISTORY 
			 WHERE RECORDSTATUS = 1 
			 AND YEARSTOPPED = 0 
			 AND
				(
				 OCCUPATION LIKE '%Dietician%'
				 OR	OCCUPATION LIKE '%homoeopath%'
				 OR OCCUPATIONCODE IN 
					(
					 239, 538, 551, 559, 560, 3052, 3059, 3299, 3346, 3554, 
					 1419, 1820, 163, 237, 559, 2561, 4007, 2112, 116, 113, 
					 582, 321, 583, 585, 3701, 278, 570, 1894, 1895, 240, 548, 
					 651, 3214, 3217, 3218, 3220, 3560, 1749, 1751, 1752, 1753, 
					 1754, 67, 1408, 280, 530, 562, 2560, 2561, 300, 534, 368, 
					 549, 1393, 1632, 1512, 1609, 1805, 555, 3554, 3555, 1988, 
					 1953, 550, 554, 558, 3502, 245, 565, -305, 1070, 4006, 105, 
					 152, 153,301, 3275, 1545, 1488, 310, 231, 714, 3198, 3199, 
					 3201, 3202, 1019, 1744, 1745, 1275, 2154, 10, 546, 2228, 531, 
					 419, 661, 365, 544, 3977, 665, 666, 1886, 668, 1324, 1099, 
					 1100, 2972, 3214, 3502, 1831, 290, 573, 574, 646, 109, 107, 
					 108, 538, 539, 540, 906, 1486, 1487, 560, 3075, 3342, 3563, 
					 1445, 1853, 1897, 3342, 1394, 3805, 557, 2552, 2173, 553, 
					 2972, 3873, 317, 577, 578, 579, 2601, 3055, 3306, 3700, 1432, 
					 1584, 2052, 2103, 368, 556, 2598, 2892, 3325, 385, 561, 1394, 
					 1612, 1806, 1833, 1838, 1864, 1940, 2033, 3805, 573, 3022, 3023, 
					 1642, 1643, 1658, 1659, 273, 529, 976, 2156, 266, 267, 268, 269, 
					 270, 563, 564, 566, 3951, 812, 3971, 2547, 2588, 2602, 4007, 2739, 
					 3301, 3343, 3482, 3483, 3484, 3553, 870, 871, 906, 922, 1038, 1419, 
					 1431, 1756, 814, 2169, 2179, 92, 663, 664, 667, 2811, 2967, 3523, 
					 1741, 1923, 2032, 2051, 2123
					)
				)
			)
	 OR InternalID IN	 
		(
		 SELECT InternalID 
		 FROM ADFOCCUPATION 
		 WHERE ADFCODE IN 
			(
			 2, -- Current Australian Defence Force - Permanent member
		 	 3  -- Current Australian Defence Force - Reserves
			)
		)
		)
	)	
OR 
	( -- Specified underlying medical conditions
	 InternalID IN 
		(
		 SELECT InternalID
		 FROM PASTHISTORY
		 WHERE RECORDSTATUS = 1
		 AND STATUSCODE = 1 -- Active
		 AND -- Haematological diseases or cancers
			(
			 ITEMCODE IN 
				(
				 2175,2174,2174,2175,2175,2223,2177,2176,2176,2177,
				 2177,1440,2178,2224,2174,2175,2176,2177,2178,2179,2173,
				 2223,2224,2226,2222,2225,2350,2226,474,3102,3047,2264,
				 3047,2179,4244,2174,5054,5054,5480,5481,6003,6426,6426,
				 6541,6541,2225,5054,11928,13181,13293,13293,13416
				)
				
			 OR	-- Non-haematological cancers within the past 5 years
				(	
				 Year >= YEAR(DATEADD(year, -5, GETDATE()))
				 AND 
					(
					 ITEMTEXT LIKE '%Cancer%' 
					 OR ITEMCODE IN 
						(
						 SELECT TERMID 
						 FROM BPSDrugs.dbo.TERM_TERMGROUPS 
						 WHERE TERMGROUPID = 7066 
						 AND TERMID NOT IN 
							(
							 0, 3745, 5438, 6877, 3745, 3746, 1074, 6205, 
							 6676, 6811, 1044, 1043, 2449, 3746, 3745, 5438, 
							 5571, 3221, 6811, 3746, 3746, 4112, 5571
							 )
						)
					)
				)

			 OR -- Adult survivors of childhood cancers
				(
				 Year < YEAR(DATEADD(YEAR, 18, DOB)) -- Diagnosed prior to 18 years old
				 AND 
					(
					 ITEMTEXT LIKE '%Cancer%' 
					 OR ITEMCODE IN 
						(
						 SELECT TERMID
						 FROM BPSDrugs.dbo.TERM_TERMGROUPS 
						 WHERE TERMGROUPID = 7066 
						 AND TERMID NOT IN 
							(
							 0, 3745, 5438, 6877, 3745, 3746, 1074, 6205, 
							 6676, 6811, 1044, 1043, 2449, 3746, 3745, 
							 5438, 5571, 3221, 6811, 3746, 3746, 4112, 5571
							)
						)
					)
				)

			 OR ITEMCODE IN -- Heart disease
				(
				 228,3576,3576,14,3579,3576,3578,2960,1534,3577,227,3579,3575,
				 1534,2556,2556,3579,3577,4746,4747,5891,6902,6902,6902,3576,
				 1534,7337,7338,9458,9464,9464,9464,7338,7338,7337,7337,9464,
				 1346,2376,3391,1081,1079,1113,273,3223,497,498,499,2080,496,
				 1347,1347,777,3223,1910,3223,933,1522,2376,1346,1347,1349,
				 1348,1350,1345,1349,1475,498,1417,3223,2379,678,679,680,2317,
				 2318,2377,2378,2379,2380,2381,2382,2376,1910,2611,2610,2609,
				 2948,1081,1934,1588,3054,1589,1079,1588,3167,217,1522,678,679,
				 680,681,677,3223,3340,3054,681,4314,1086,4509,4738,4739,4953,
				 499,1417,5071,2948,5116,5116,1348,1079,1113,1086,497,497,497,
				 5179,5180,5181,5182,5183,5184,5183,5179,5182,5184,5181,5185
				)

			 OR ITEMCODE IN -- Chronic lung disease
				(
				 463,464,465,466,467,462,4311,694,3894,177,3063,1111,1294,3819,
				 66,462,694,2293,3816,3826,2440,2775,3818,3826,4310,2293,4662,
				 4675,1679,4756,4779,2759,4851,4870,4913,4915,4921,4952,4985,
				 5029,66,462,69,1294,5813,6830,7046,7944,8206,9500,11754,12062
				)

			 OR ITEMCODE IN -- Diabetes
				(
				 3,775,774,773,772,780,776,778,776,778,774,780,776,776,773,778,
				 2985,773,775,776,780,778,5772,5772,5772,7572,7572,5772,7840,
				 7973,8146,8146,11998,11998,11998
				)

			 OR ITEMCODE IN -- Chronic liver disease
				(
				 1378,501,502,2039,784,1350,1722,501,1722,1723,502,1723,
				 584,2195,587,5010,5184,5184,6279,7548,1376,7548,7548,
				 9419,9419,11849,11926,12007
				)
			
			 OR ITEMCODE IN -- Chronic neurological conditions
				(
				 2321,1219,288,826,1605,518,1219,513,677,1690,751,752,751,750,2364,
				 1607,904,1583,965,966,968,969,971,963,976,828,2365,2308,1219,965,
				 965,1219,1608,966,966,966,966,1522,2309,1219,1584,1610,1584,1428,
				 678,968,968,588,2042,2085,679,1648,3389,1606,1608,1609,1611,1612,
				 1613,1604,680,2321,751,2351,2368,2364,2365,2363,2310,1024,2627,969,
				 3084,966,2321,971,971,3254,827,969,3406,1522,678,679,680,681,677,
				 3389,971,971,681,4215,967,4312,751,1613,828,4527,966,647,4623,4623,
				 4957,4957,5014,5122,966,966,5232,5232,1612,1611,1609,1609,1606,5336,
				 5336,6045,6046,971,6180,6215,6233,6233,6233,6233,6237,4680,976,6283,
				 1584,1584,6364,2042,5122,5122,5122,6367,6410,6410,6547,4312,6590,
				 6590,6591,6591,6592,6592,6593,6593,6594,6594,6594,6594,2364,2364,
				 2364,6595,6595,6595,6596,6596,6604,6604,6688,5902,4550,6805,7092,
				 7099,7099,7100,7100,7167,7167,7419,7500,7500,7779,7779,7805,7839,
				 7839,7878,8072,8078,8158,8158,8196,6233,9376,9376,9388,9388,9390,
				 9390,6590,10532,10664,10664,10665,10665,11720,6688
				)
			
			 OR ITEMCODE IN -- Significant disability requiring frequent assistance with activities of daily living
				(
				 3767,2819,3767,3768,3700,4130,3668,3879,302,3768,487,513,2023,1451,720,961,959,868,
				 3668,1371,3700,1450,1461,1462,1461,621,2099,2099,2197,1459,487,2284,3601,487,2559,
				 487,2630,3869,3879,3105,2819,2816,2817,2818,2820,2821,2815,4421,1452,3090,3090,
				 3093,3105,1453,2022,2002,3602,4198,4199,2469,487,487,2469,487,487,4421,2821,4649,
				 4829,4855,5016,5017,5081,5124,5124,1452,1453,302,5217,5217,5260,5314,5401,5402,
				 5403,487,5506,5516,5521,5522,5678,5678,5678,5679,5680,5684,5684,5422,5728,5729,
				 5729,5744,5786,5814,5814,5885,5885,5886,5886,6009,3093,6188,6207,6216,6224,6234,
				 6234,6234,6234,6254,6258,6265,6274,6300,6305,6314,6378,6395,6417,6422,5549,1418,
				 712,6450,6450,6504,6504,6512,6536,6547,6579,6629,6638,6648,6652,6664,6664,6664,
				 6664,3041,6680,6680,6688,6689,6690,6691,5214,6715,6716,6717,6341,6781,6782,6787,
				 6791,6791,6834,6834,7020,7053,7089,7166,7217,7575,7595,7664,7667,7773,7785,7825,
				 7825,7859,7859,7924,7933,8050,8051,8052,9391,9391,5124,5124,726,2816,6688,6688,
				 11955,12071,12071,12110,12111,13198,2793
				)
			
			 OR -- Severe Mental Health Conditions
				(
				 ITEMCODE IN 
					(
					 SELECT TERMID 
					 FROM BPSDrugs.dbo.TERM_TERMGROUPS 
					 WHERE TERMGROUPID = 3233 
					 AND TERMID NOT IN (247)
					)
			 OR ITEMCODE IN 
				(
				 3056, 3057, 2262, 3056, 3057, 3055, 3254, 8072, 8072, 6599, 
				 5200, 2262, 2245, 5206, 302, 5217, 5217, 6207, 5205, 247
				)
			
			 OR ITEMCODE IN -- Chronic inflammatory conditions
				(
				 285,189,233,4449,2762,2757,282,1378,414,1678,2763,419,598,598,
				 508,598,2758,702,763,3341,929,928,2759,3051,3051,1261,196,2765,
				 1519,4450,2760,3171,2766,2767,3389,2260,2768,2321,2351,1918,1401,
				 2559,203,2752,763,587,1507,2764,3051,3053,3050,265,3053,3171,3170,
				 3222,3243,1918,1508,3050,3341,3340,1381,3347,1876,285,3340,1261,
				 4338,4448,1896,3327,598,4651,4652,4740,4869,4889,4908,4979,5010,
				 5021,5120,5121,282,929,419,5417,1896,5542,5583,5593,5596,5617,5619,
				 5648,5648,5648,5648,5649,5649,5650,5650,5654,5658,5654,5654,5690,
				 5691,3051,3051,5691,5713,5713,189,5747,5747,5747,5747,5965,5965,
				 6034,6034,6039,6103,3347,6251,6279,6283,6033,6363,4740,4740,4740,
				 4740,4806,6525,6525,6526,6526,4869,6656,6666,3041,2760,2760,2760,
				 2760,2760,3047,3047,577,3168,6793,6793,6794,6794,6848,6848,7182,
				 7588,7588,7748,7757,7811,7811,7869,8015,8015,8015,8045,8047,8070,
				 8071,8071,8070,8112,8163,8163,8164,8164,267,3347,8337,9443,9503,
				 10685,10685,12119,12119,12119,12137,13156,13156,13226,13226,3959,
				 3957,5061,5062,5061,6493,6981,8320,11789
				)
			
			 OR ITEMCODE IN -- Primary or acquired immunodeficiency
				(
				 1727, 1545, 1547, 1548, 1549, 1727, 1545, 1547, 1548, 1549, 1551,
				 1552, 1544, 1551, 1549, 1552, 5108, 6292, 6737, 6740, 6741, 6742, 
				 6741, 6632, 1727, 1727, 6189, 13279, 13280, 1727, 4319, 6189
				))
			)
		
		OR CAST -- Latest BMI >= 40
			(
				(
				 SELECT TOP 1 DATAVALUE 
				 FROM OBSERVATIONS 
				 WHERE RECORDSTATUS = 1 
				 AND DATACODE = 9 
				 AND InternalID = BPS_Patients.InternalID
				 AND ISNUMERIC(DATAVALUE) = 1 
				 ORDER BY OBSDATE DESC
				) AS FLOAT
			) >= 40.0
		
		OR CAST -- Chronic renal (kidney) failure with a eGFR of <44mL/min
			(
				(
				 SELECT TOP 1 ISNUMERIC(RESULTVALUE) 
				 FROM REPORTVALUES
				 WHERE RECORDSTATUS = 1 
				 AND RESULTNAME = 'eGFR' 
				 AND UNITS = 'mL/min' 
				 AND InternalID = BPS_Patients.InternalID 
				 AND ISNUMERIC(RESULTVALUE) = 1 
				 ORDER BY REPORTDATE DESC
				) AS FLOAT
			) < 44.0
		
		OR -- Poorly controlled blood pressure (defined as two or more pharmacologic agents for blood pressure control, regardless of recent readings)
			(
				(
				 SELECT	COUNT(InternalID) 
				 FROM CURRENTRX 
				 WHERE RECORDSTATUS = 1
				 AND InternalID = BPS_Patients.InternalID
				 AND 
					(
					 PRODUCTID IN 
						(
						 5,6,7,28,29,30,33,34,39,40,41,124,125,126,127,128,133,134,135,136,167,168,169,196,197,266,267,268,312,329,330,377,378,379,406,407,408,409,452,528,529,530,564,565,566,567,568,569,570,621,622,623,636,637,638,639,640,702,703,723,779,783,784,785,789,797,798,799,816,817,818,
						 876,914,915,968,976,1074,1081,1082,1083,1084,1089,1090,1091,1092,1093,1094,1095,1102,1103,1104,1105,1106,1132,1133,1134,1135,1136,1149,1150,1151,1152,1313,1316,1326,1328,1331,1338,1341,1343,1352,1699,1700,1701,1705,1706,1725,1734,1735,1736,1739,1947,2078,2079,2080,2177,
						 2252,2253,2254,2255,2266,2267,2268,2269,2270,2271,2272,2353,2527,2528,2586,2587,2588,2589,2590,2591,2592,2593,2594,2595,2596,2597,2598,2661,2684,2702,2703,2813,2814,2815,2816,2817,2818,2867,2868,2869,3037,3038,3039,3052,3053,3054,3055,3056,3057,3058,3059,3060,3061,3063,
						 3064,3156,3160,3161,3162,3179,3180,3181,3184,3185,3186,3190,3191,3203,3204,3205,3209,3210,3213,3226,3227,3357,3358,3359,3463,3465,3470,3472,3474,3481,3484,3486,3493,3616,3617,3761,3762,3872,3873,3874,3875,3897,3898,3899,3900,3901,3902,3903,3964,3990,3991,3992,3993,3994,
						 4048,4049,4050,4051,4065,4127,4128,4129,4130,4131,4132,4133,4135,4163,4196,4254,4255,4256,4265,4266,4267,4268,4269,4270,4271,4272,4273,4274,4275,4314,4315,4321,4665,4666,4667,4668,4670,4671,4675,4698,4699,4700,4701,4733,4756,4757,4840,4852,4853,4854,4855,5154,5155,5156,
						 5157,5158,5159,5160,5161,5172,5173,5174,5175,5224,5225,5228,5314,5335,5336,5337,5501,5726,5824,5825,5860,5861,5862,6012,6013,6036,6037,6038,6040,6041,6111,6219,6220,6221,6222,6250,6251,6252,6253,6254,6256,6257,6315,6316,6317,6318,6818,6819,6885,6886,6887,6888,6889,6890,
						 6891,6892,6904,6905,6910,6911,7143,7144,7145,7146,7147,7170,7171,7172,7237,7238,7239,7240,7260,7286,7287,7288,7340,7341,7342,7343,7344,7345,7346,7347,7348,7349,7394,7395,7396,7427,7428,7429,7430,7431,7495,7496,7497,7498,7499,7500,7582,7583,7584,7585,7641,7642,7643,7644,
						 7667,7668,7669,7670,7671,7672,7729,7730,7787,7848,7849,7876,7877,7878,7879,7880,8027,8028,8029,8030,8031,8032,8033,8034,8055,8077,8081,8082,8118,8119,8130,8136,8180,8204,8206,8207,8208,8254,8255,8266,8267,8270,8271,8272,8273,8286,8287,8288,8310,8311,8312,8314,8338,8339,
						 8340,8341,8342,8343,8344,8345,8346,8347,8348,8374,8376,8382,8386,8422,8423,8438,8458,8464,8465,8467,8545,8548,8549,8554,8557,8558,8559,8561,8564,8600,8703,8813,8869,8875,8978,9136,9137,9142,9145,9154,9155,9156,9176,9217,9218,9242,9243,9406,9452,9453,9454,9467,9468,9469,
						 9471,9472,9564,9565,9593,9594,9655,9656,9657,9658,9684,9707,9708,9761,9788,9789,9855,9856,9857,9858,9910,9911,9912,9913,9914,9915,9937,9962,9963,9964,10124,10125,10126,10127,10128,10129,10130,10131,10139,10140,10141,10142,10143,10144,10145,10146,10182,10183,10184,10185,
						 10186,10187,10199,10200,10201,10202,10203,10204,10205,10206,10207,10208,10209,10210,10211,10212,10213,10239,10240,10303,10314,10315,10316,10317,10318,10319,10320,10321,10324,10325,10326,10345,10346,10352,10353,10354,10355,10356,10357,10358,10359,10360,10361,10367,10368,10369,
						 10370,10429,10430,10445,10446,10447,10448,10449,10450,10454,10455,10457,10458,10459,10470,10471,10472,10473,10474,10475,10476,10482,10483,10485,10486,10489,10491,10492,10493,10504,10515,10516,10517,10518,10519,10520,10647,10648,10649,10650,10678,10679,10680,10681,10751,10752,
						 10753,10754,10826,10827,10828,10829,10830,10831,10838,10839,10840,10848,10849,10850,10851,10852,10853,10854,10870,10871,10872,10873,10886,10887,10893,10894,10895,10930,10931,10932,10933,11006,11007,11008,11009,11010,11011,11012,11022,11023,11024,11025,11075,11076,11102,11103,
						 11104,11141,11142,11143,11193,11194,11288,11289,11290,11291,11405,11406,11407,11430,11431,11432,11433,11439,11440,11441,11442,11443,11444,11464,11465,11466,11477,11478,11479,11480,11481,11482,11483,11526,11527,11528,11529,11530,11531,11532,11533,11550,11551,11552,11553,11570,
						 11603,11628,11704,11705,11706,11707,11708,11709,11737,11738,11739,11740,11741,11742,11743,11744,11745,11746,11787,11788,11789,11790,11792,11793,11815,11816,11817,11820,11821,11822,11823,11824,11825,11884,11885,11886,11923,11924,11985,11986,11987,11988,11989,11990,11991,11992,
						 12015,12016,12017,12070,12071,12072,12073,12075,12076,12179,12180,12181,12311,12320,12390,12391,12392,12413,12414,12415,12416,12425,12426,12427,12428,12429,12430,12431,12432,12445,12446,12447,12448,12449,12450,12451,12525,12575,12576,12577,12580,12624,12664,12671,12700,12701,
						 12702,12745,12746,12789,12790,12791,12792,12793,12794,12795,12796,12797,12798,12799,12800,12801,12802,12803,12804,12805,12806,12846,12847,12848,12912,12913,12914,12915,12916,12947,12948,12949,13119,13120,13124,13125,13126,13127,13130,13131,13132,13133,13134,13135,13136,13137,
						 13138,13139,13167,13168,13169,13170,14415,14416,14431,14432,14433,14434,14435,14436,14437,14438,14455,14456,14487,14488,14489,14490,14491,14492,14511,14512,14513,14516,14517,14518,14522,14523,14524,14525,14554,14555,14556,14557,14558,14560,14561,14562,14563,14564,14565,14566,
						 14567,14568,14569,15791,15797,15798,15800,15804,16861,16862,16863,16864,16865,16866,16867,16868,16869,16880,16881,16882,16883,16991,17115,17116,17117,17118,17119,17164,17165,17166,17212,17213,17214,17215,17216,17217,17350,17351,17450,17451,17452,17454,17455,17456,17457,17458,
						 17504,17505,17506,17507,17509,17510,17511,17512,17516,17517,17520,17521,17522,17523,17537,17538,17539,17540,18701,18713,19749,19750,19751,19752,19753,19754,19755,20820,20824,20945,20946,20947,20958,20959,20994,20995,21082,22293,23350,23351,23352,23353,23378,23379,23395,23396,
						 23420,23598,23599,23910,23911,25034,25035,25036,25257,25387,25388,25493,25529,25530,25531,25532,25579,25580,25622,25627,25628,25629,25631,25632,25633,25748,25749,25750
						)
					 OR PRODUCTID IN 
						(
						 SELECT	PRODUCTID
						 FROM BPSDrugs.dbo.PRODUCTS p INNER JOIN BPSDrugs.dbo.PRODUCTNAMES pm ON p.PRODUCTNAMEID = pm.PRODUCTNAMEID
						 WHERE pm.PRODUCTNAME IN 
							(
							 'hydralazine'
							 ,'minoxidil'
							 ,'prazosin'
							 ,'methyldopa'
							 ,'clonidine'
							 ,'carvedilol'
							 ,'labetalol'
							) -- Products can be added or removed as needed as long as they are surrounded by single quotes and separated by a comma.
						)
					)
				) >= 2
			)
		)
	)
ORDER BY Surname, Firstname;