select first_name, last_name,
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) as "Age",
CONCAT( "/", demographic_no, "/" ) AS "DemoLink",
(select MAX(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status LIKE '%B%') as "Last appointment",
(select IF((select MAX(a1. appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')>DATE("2020-03-15"), "*", "")) as " ",
(select MIN(a.appointment_date) from appointment a where a.demographic_no=demo.demographic_no and a.status NOT LIKE '%C%' and a.status NOT LIKE '%X%' AND a.appointment_date>NOW()) as "Next appointment",
demo.phone as "Phone",
(select value FROM demographicExt WHERE demographic_no=demo.demographic_no AND key_val="demo_cell" limit 1) as "Cell",
demo.email as "Email"
FROM demographic demo
WHERE demo.patient_status='AC'
AND demo.provider_no={provider}
AND
(
(("{last_seen}"=""))
OR
(("{last_seen}"!="") AND (select MAX(a1.appointment_date) from appointment a1 where a1.demographic_no=demo.demographic_no and a1.status LIKE '%B%')= 0{age_g})
OR
("{age_g}"="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) >= 80)
)
AND(
("{age_l}"!="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demo.year_of_birth , "-", demo.month_of_birth, "-", demo.date_of_birth)), NOW()) < 0{age_l})
OR
("{age_l}"="")
)
AND(
# diagnoses check
("{dx_code}" = "")
OR
(
("{dx_code}" != "") AND
exists (select dxresearch_no from dxresearch where dxresearch.demographic_no = demo.demographic_no and dxresearch.status='A' and dxresearch.dxresearch_code in (0{dx_code}))
)
OR
(
("{dx_code}" != "") AND
exists (select billingmaster_no from billingmaster where billingmaster.demographic_no = demo.demographic_no and billingmaster.billingstatus IN ("B", "S", "O") AND TIMESTAMPDIFF(YEAR, DATE(billingmaster.service_date), NOW()) <=2 AND billingmaster.dx_code1 in (0{dx_code}))
)
)
order by (case when '{sort}' = 'last_name' then demo.last_name end) asc,
(case when '{sort}' = 'age' then DATE(CONCAT(demo.year_of_birth, "-", demo.month_of_birth, "-", demo.date_of_birth)) end) asc
select provider_no, CONCAT(last_name) from provider WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1
ORDER BY last_name;
Last Name
Age