SELECT "min value 0.1%" AS "", -- TOTAL PATIENTS IN THE CLINIC OLDER THAN 16 COUNT(fin.patient) AS "Patients > 16 yrs", -- PERCENT OF TOTAL > 16 PATIENTS WITH SMOKING NOTATIONS. IF( COUNT(fin.patient) > 0, ROUND( COUNT(fin.smk) * 100 / COUNT(fin.patient), 1 ), 0 ) AS "Smokers (%)", -- PERCENT OF NOTATED SMOKING PATIENTS WITH A SMOKING BILLING ID IF( COUNT(fin.patient) > 0, ROUND( COUNT(fin.e079) * 100 / COUNT(fin.smk), 1 ), 0 ) AS "E079 (%)" FROM ( SELECT d.demographic_no AS patient, SMK.id AS smk, BILL.id AS e079 FROM demographic d -- MEASUREMENT NOTATIONS FOR SMOKERS. LEFT JOIN( SELECT demographicNo, ID FROM measurements m WHERE type LIKE "%SMK%" AND dataField LIKE "%Y%" GROUP BY demographicNo HAVING COUNT(demographicNo) > -1 ) SMK ON (SMK.demographicNo = d.demographic_no) -- LOOK IN THE PATIENT INVOICES FOR E079 SERVICE FEES RELATED TO SMOKING LEFT JOIN ( SELECT boc.demographic_no, boc.id FROM billing_on_cheader1 boc INNER JOIN billing_on_item E079 ON (E079.ch1_id = boc.id AND E079.service_code LIKE "%E079%") WHERE( DATE(E079.service_date) BETWEEN DATE("{FROM}") AND DATE("{TO}") ) GROUP BY boc.demographic_no HAVING COUNT(boc.demographic_no) > -1 ) BILL ON (BILL.demographic_no = d.demographic_no) WHERE d.patient_status LIKE "%AC%" -- PATIENT SHOULD BE OLDER THAN 16 AS OF THE EARLIEST DATE IN THE DATE RANGE. AND ROUND( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,"-",d.month_of_birth,"-",d.date_of_birth) ), DATE("{FROM}") ) / 365.25 ) ) > 16 GROUP BY d.demographic_no HAVING COUNT(d.demographic_no) > -1 ) fin;