select
demographic.last_name as "Last Name",demographic.first_name as "First Name", demographic.hin as "HIN", CONCAT(demographic.year_of_birth,'-', demographic.month_of_birth,'-', demographic.date_of_birth) as "DOB", timestampdiff(year,date(concat(demographic.year_of_birth,"-",demographic.month_of_birth,"-",demographic.date_of_birth)),NOW()) as "Age", demographic.sex as "Gender",(select demographicExt.value from demographicExt
WHERE demographicExt.demographic_no=demographic.demographic_no AND demographicExt.key_val="demo_cell"
and demographicExt.id IN (select max(id) from demographicExt where demographicExt.key_val="demo_cell" group by demographicExt.demographic_no)) as "Cell Phone",demographic.phone as "Home Phone", demographic.phone2 as "Work Phone", demographic.email as Email, demographic.patient_status as "Status", demographic.roster_status as "Roster Status",
(select
CONCAT(provider.last_name,", ",provider.first_name) from provider where demographic.provider_no=provider.provider_no) as "Provider"
FROM demographic
WHERE
demographic.provider_no LIKE {provider}
AND demographic.patient_status="AC"
AND
# age check:
(
("{age_g}"!="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demographic.year_of_birth , "-", demographic.month_of_birth, "-", demographic.date_of_birth)), NOW()) >= 0{age_g})
OR
("{age_g}"="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demographic.year_of_birth , "-", demographic.month_of_birth, "-", demographic.date_of_birth)), NOW()) >= 12)
)
AND(
("{age_l}"!="" AND TIMESTAMPDIFF(YEAR, DATE(CONCAT(demographic.year_of_birth , "-", demographic.month_of_birth, "-", demographic.date_of_birth)), NOW()) < 0{age_l})
OR
("{age_l}"="")
)
#check if impossible age
AND
TIMESTAMPDIFF(YEAR, DATE(CONCAT(demographic.year_of_birth , "-", demographic.month_of_birth, "-", demographic.date_of_birth)), NOW()) < 110
# Eliminate terminated patients
and demographic.roster_status<>"TE"
order by (case when '{sort}' = 'last_name' then demographic.last_name end) asc,
(case when '{sort}' = 'age' then DATE(CONCAT(demographic.year_of_birth, "-", demographic.month_of_birth, "-", demographic.date_of_birth)) end) asc
;
select "'%'" as provider_no, 'All Providers' as provider
union
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;
Last Name
Age