SELECT "min value 0.1%" AS "",
-- PATIENT LIST AS COMMON DENOMINATOR
COUNT(fin.patient) AS "Female Patients Aged [50,75]",
-- COUNT ALL PATIENTS WITH MAMMOGRAMS PERFORMED
IF( COUNT(fin.patient) > 0, ROUND( ( COUNT(fin.mam) * 100 ) / COUNT(fin.patient), 1 ), 0)AS "MAM Since {FROM} (%)",
-- COUNT ALL ABNORMAL RESULTS
IF( COUNT(fin.patient) > 0, IFNULL( ROUND( SUM(fin.abn = "abnormal") * 100 / COUNT(fin.patient), 1 ), 0 ), 0) AS "MAM Abn (%)"
FROM (
SELECT
d.demographic_no AS patient,
MAM.id AS mam,
MAM.val AS abn
FROM demographic d
-- GET ALL MAMOGRAM ENTRIES FROM PREVENTIONS
LEFT JOIN ( SELECT p.demographic_no, p.id, pe.val
FROM preventions p
LEFT JOIN preventionsExt pe
ON (pe.prevention_id = p.id AND pe.keyval LIKE "%result%")
WHERE p.prevention_type LIKE "%MAM%"
AND p.deleted = 0
AND DATE(p.prevention_date) >= DATE("{FROM}")
ORDER BY pe.val ASC ) MAM
ON (d.demographic_no = MAM.demographic_no)
-- WHERE THE PATIENT'S AGE AT THE TIME OF THE COLONOSCOPY
WHERE d.patient_status LIKE "%AC%"
AND d.sex LIKE "%F%"
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;