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