SELECT "min value 0.1%" AS "",
-- PATIENT LIST AS COMMON DENOMINATOR
COUNT(fin.patient) AS "Patients Aged [50,75]",
-- COUNT ALL PATIENTS WITH COLONOSCOPYS PERFORMED
IF( COUNT(fin.patient) > 0, ROUND( ( COUNT(fin.col) * 100 ) / COUNT(fin.patient), 1 ), 0) AS "COL Since {FROM} (%)",
-- COUNT ALL ABNORMAL RESULTS
IF( COUNT(fin.patient) > 0, IFNULL( ROUND( SUM(fin.abn LIKE "abnormal%") * 100 / COUNT(fin.patient), 1 ), 0), 0) AS "COL Abn (%)"
FROM (
SELECT
d.demographic_no AS patient,
prev.id AS col,
prev.val AS abn
FROM demographic d
-- LOOK FOR ENTRIES IN THE PREVENTIONS TABLE
LEFT JOIN ( SELECT p.demographic_no, p.id, pe.val
FROM preventions p
INNER JOIN preventionsExt pe
ON (pe.prevention_id = p.id AND pe.keyval LIKE "%result%")
WHERE p.prevention_type LIKE "%COLONOSCOPY%"
AND p.deleted = 0
AND DATE(p.prevention_date) > DATE("{FROM}")
ORDER BY pe.val ASC ) prev
ON (d.demographic_no = prev.demographic_no )
-- WHERE THE PATIENT'S AGE AT THE TIME OF THE COLONOSCOPY
WHERE d.patient_status LIKE "%AC%"
AND 50 <= ROUND( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ), DATE("{FROM}") ) / 365.25 ) )
AND 75 >= ROUND( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ), DATE("{FROM}") ) / 365.25 ) )
GROUP BY d.demographic_no HAVING COUNT(d.demographic_no) > -1
) fin;