( SELECT CONCAT("Active patients assigned to doctor ::: ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 1 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (1*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 2 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (2*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 3 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (3*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 4 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (4*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 5 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (5*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 6 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (6*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 7 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (7*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 8 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (8*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 9 years ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (9*12) MONTH ) ) ) UNION ALL ( SELECT CONCAT("Active patients not seen in last 10 year ",count(d.demographic_no) ) as Number FROM demographic d WHERE patient_status = 'AC' AND d.provider_no = '{provider_no}' AND d.demographic_no NOT IN ( SELECT d.demographic_no AS Count FROM demographic AS d INNER JOIN eChart AS e ON d.demographic_no = e.demographicNo WHERE d.patient_status = 'AC' AND e.`timeStamp` >= DATE_SUB( NOW(), INTERVAL (10*12) MONTH ) ) ) ; select provider_no,concat(last_name,',',first_name,'(',provider_no,')') from provider where status='1' and provider_type='doctor' and ohip_no>1 order by provider_no;