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;