SELECT d.demographic_no AS "ID",
CONCAT(d.last_name, ", ", d.first_name) AS "Name",
HTN.dxresearch_code AS "DxCode"
FROM demographic d
-- ROOT DETERMINED HYPERTENSION NOTATIONS
INNER JOIN dxresearch HTN
ON ( d.demographic_no = HTN.demographic_no )
INNER JOIN casemgmt_issue ci
ON(d.demographic_no = ci.demographic_no)
LEFT JOIN casemgmt_issue_notes n
ON ( n.id = ci.id AND ci.issue_id = (SELECT issue_id FROM issue WHERE code LIKE "MedHistory%") )
-- LOOK FOR AN ENTRY IN RISK FACTORS AS "SMOKER"
LEFT JOIN casemgmt_note cn
ON (cn.note_id = n.note_id )
WHERE d.patient_status LIKE "%AC%"
AND (
(DATE(HTN.start_date) BETWEEN DATE("{FROM}") AND DATE("{TO}") )
AND HTN.coding_system LIKE "%icd9%"
AND HTN.dxresearch_code LIKE "401"
AND HTN.status NOT LIKE "%D%"
) OR (
DATE(cn.observation_date) BETWEEN DATE("{FROM}") AND DATE("{TO}")
AND cn.archived = 0
AND cn.signed = 1
AND cn.history LIKE "% HTN %"
)
GROUP BY d.demographic_no HAVING COUNT(d.demographic_no) > -1;