SELECT * FROM ( SELECT CONCAT( "", efd.demographic_no, "" ) AS "Master", CONCAT( "",d.last_name,", ",SUBSTRING_INDEX(d.first_name," ",1), "" ) AS "eChart", CONCAT( "", CONCAT(LEFT(efd.form_name,5),efd.fdid),"") AS "eForm", efd.form_date AS "form date", CONCAT( "", CONCAT(pre.prevention_type, pre.id),"" ) as prevention, LEFT(pre.prevention_date,10) AS "prevention date", CONCAT (pre.deleted,"/",pre.refused) AS "del/refuse", prx.val, CONCAT( "",CONCAT(hl7.discipline,hl7.accessionNum),"" ) as test, LEFT(hl7.obr_date,10) AS "test date", d.provider_no AS num, p.last_name AS "Provider Surname", d.family_doctor AS FP FROM eform_data efd LEFT JOIN preventions pre ON ( efd.demographic_no = pre.demographic_no AND pre.creation_date > efd.form_date AND pre.prevention_type = SUBSTRING_INDEX(SUBSTRING_INDEX('{testcode}', '~', 2), '~', -1)) LEFT JOIN preventionsExt prx ON ( prx.prevention_id = pre.id AND prx.keyval = "result") LEFT JOIN demographic d ON ( efd.demographic_no = d.demographic_no ) LEFT JOIN provider p ON ( d.provider_no = p.provider_no ) LEFT JOIN patientLabRouting plr ON ( efd.demographic_no = plr.demographic_no AND plr.created > efd.form_date ) LEFT JOIN hl7TextInfo hl7 ON ( plr.lab_no = hl7.lab_no AND hl7.obr_date >= efd.form_date AND hl7.discipline REGEXP SUBSTRING_INDEX(SUBSTRING_INDEX('{testcode}', '~', 3), '~', -1)) WHERE efd.fid IN(SUBSTRING_INDEX('{testcode}', '~', 1)) AND efd.form_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-{months})*100)+1) AND (d.provider_no LIKE {provider_no}) ORDER BY efd.form_date DESC, accessionNum DESC, pre.id DESC LIMIT 10000 ) AS sub GROUP BY eForm ORDER BY sub.`form date` DESC ; 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>1000 ORDER BY provider_no; SELECT CONCAT(fid,"~PAP~AP\/C*|SRC\/") AS testcode, CONCAT("PAP|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.status=1 AND (efm.form_name LIKE "%PAP%" OR efm.form_name LIKE "%cytolo%" ) UNION SELECT CONCAT(fid,"~FOBT~COLON%") AS testcode, CONCAT("FIT|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.form_name LIKE "FIT%" AND efm.status=1 UNION SELECT CONCAT(fid,"~MAM~mamm%") AS testcode, CONCAT("MAM|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.form_name LIKE "mammo%" AND efm.status=1 ;