(
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;