SELECT
demo.last_name as "Last Name ",
demo.first_name as "First Name",
bm.dx_code1 as "DxCode",
bm.service_date,
CONCAT( "/", demo.demographic_no, "/" ) AS "DemoLink",
CONCAT( "/", demo.first_name, "/" ) AS "eChart",
(select concat(pr.first_name, ' ', pr.last_name) from provider pr where pr.provider_no = demo.provider_no) as Provider
FROM
billingmaster bm,
demographic demo
WHERE
bm.dx_code1 in ('{code}')
AND provider_no in ({provider_no})
AND demo.patient_status = 'AC'
AND demo.demographic_no = bm.demographic_no
AND STR_TO_DATE(bm.service_date,'%Y%m%d') >= '{service_date}'
AND STR_TO_DATE(bm.service_date,'%Y%m%d') <= '{service_date2}'
GROUP BY demo.last_name, demo.first_name, bm.dx_code1 ;
select provider_no, CONCAT(last_name) from provider WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1
ORDER BY last_name;