SELECT "Note: min value is 0.1%" AS "", -- TOTAL NUMBER OF DM PATIENTS COUNT(fin.patient) AS "DM Patients", -- % WITH A1C MEASUREMENTS IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.a1c > 0 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient) , 1 ), 0) AS "HbA1c (%)", -- % WITH 2 A1C MEASUREMENTS IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.a1c > 1 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient) , 1 ), 0) AS "HbA1c 2x (%)", -- % WITH A1C LAB REQS IN THE FIRST 9 MONTHS OF DX IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.req9 > 0 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient), 1 ), 0) AS "HbA1c Lab Req. 9 months (%)", -- % OF ABOVE WITH A1C MEASUREMENTS. THIS IS A BEST ESTIMATE. THERE IS NO DATA RELATIONSHIP IF ( SUM(fin.req) > 0, ROUND( SUM(fin.a1c9) * 100 / SUM(fin.req9) , 1 ), 0) AS "9 month HbA1c Labs Complete (%)", -- % WITH LDL LESS THAN 2 IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.ldl > 0 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient) , 1 ), 0) AS "LDL < 2.0 (%)", -- WITH LDL LESS THAN 2 IN FIRST 3 MONTHS IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.ldl3 > 0 THEN 1 ELSE 0 END )* 100 / COUNT(fin.patient) , 1 ), 0) AS "LDL < 2.0 3 months (%)", -- TAKING AN ACE IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.ace > 0 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient), 1 ), 0) AS "ACE (%)", -- TAKING A STATIN IF ( COUNT(fin.patient) > 0, ROUND( SUM( CASE WHEN fin.stat > 0 THEN 1 ELSE 0 END ) * 100 / COUNT(fin.patient), 1 ), 0) AS "Statin (%)", -- NUMBER OF LABS ISSUED VS. NUMBER WITH RESULTS. IF ( SUM(fin.req) > 0, ROUND( SUM(fin.a1c) * 100 / SUM(fin.req) , 1 ), 0) AS "HbA1c Labs Completed (%)" FROM ( SELECT d.demographic_no AS patient, A1C.a1cnumber AS a1c, A1C9.a1c9number AS a1c9, LDL.ldlnumber AS ldl, LDL3.ldl3number AS ldl3, ACE.acenumber AS ace, STAT.statnumber AS stat, REQ.reqnumber AS req, REQ9.req9number AS req9 FROM demographic d -- ROOT CONTROLS THE IDENTIFICATION OF ALL DIABETIC PATIENTS. INNER JOIN dxresearch dxr ON ( d.demographic_no = dxr.demographic_no) -- COUNT ALL A1C MEASUREMENTS. COUNTS ALL TESTS PER PATIENT. LEFT JOIN ( SELECT COUNT(*) AS a1cnumber, demographicNo FROM measurements WHERE type LIKE "A1C" AND ( DATE(dateObserved) BETWEEN DATE("{FROM}") AND DATE("{TO}") ) AND demographicNo > 0 GROUP BY demographicNo HAVING COUNT(demographicNo) > -1 ) A1C ON (d.demographic_no = A1C.demographicNo) -- PATIENTS FROM THE FIRST 9 MONTHS WITH A1C MEASUREMENTS LEFT JOIN ( SELECT COUNT(*) AS a1c9number, demographicNo FROM measurements WHERE type LIKE "A1C" AND ( DATE(dateObserved) BETWEEN DATE("{FROM}") AND DATE_ADD( DATE("{FROM}"), INTERVAL 9 MONTH ) ) AND demographicNo > 0 GROUP BY demographicNo HAVING COUNT(demographicNo) > -1 ) A1C9 ON (d.demographic_no = A1C9.demographicNo) -- ALL LAB REQS DONE IN THE FIRST 9 MONTHS. LEFT JOIN ( SELECT COUNT(*) AS req9number, demographic_no FROM formLabReq10 WHERE b_hba1c = 1 AND ( DATE(formCreated) BETWEEN DATE("{FROM}") AND DATE_ADD( DATE("{FROM}"), INTERVAL 9 MONTH ) ) AND demographic_no > 0 GROUP BY demographic_no HAVING COUNT(demographic_no) > -1) REQ9 ON(d.demographic_no = REQ9.demographic_no) -- ALL LDL. COUNT FOR 1 IF ANY LDL < 2 IF FOUND. LEFT JOIN ( SELECT COUNT(*) AS ldlnumber, demographicNo FROM measurements WHERE type LIKE "LDL" AND dataField < 2 AND DATE(dateObserved) BETWEEN DATE("{FROM}") AND DATE("{TO}") AND demographicNo > 0 GROUP BY demographicNo HAVING COUNT(demographicNo) > -1 ) LDL ON(d.demographic_no = LDL.demographicNo) -- LDL FIRST 3 MONTHS. COUNT FOR 1 IF ANY LDL < 2 IF FOUND. LEFT JOIN ( SELECT COUNT(*) AS ldl3number, demographicNo FROM measurements WHERE type LIKE "LDL" AND dataField < 2 AND ( DATE(dateObserved) BETWEEN DATE("{FROM}") AND DATE_ADD( DATE("{FROM}"), INTERVAL 3 MONTH ) ) AND demographicNo > 0 GROUP BY demographicNo HAVING COUNT(demographicNo) > -1 ) LDL3 ON(d.demographic_no = LDL3.demographicNo) -- TAKING AND ACE LEFT JOIN ( SELECT COUNT(*) AS acenumber, demographic_no FROM drugs WHERE atc LIKE "C09AA%" AND archived = 0 AND DATE(rx_date) BETWEEN DATE("{FROM}") AND DATE("{TO}") AND demographic_no > 0 GROUP BY demographic_no HAVING COUNT(demographic_no) > -1) ACE ON(d.demographic_no = ACE.demographic_no) -- TAKING A STATIN LEFT JOIN ( SELECT COUNT(*) AS statnumber, demographic_no FROM drugs WHERE atc LIKE "C10AA%" AND archived = 0 AND DATE(rx_date) BETWEEN DATE("{FROM}") AND DATE("{TO}") AND demographic_no > 0 GROUP BY demographic_no HAVING COUNT(demographic_no) > -1) STAT ON(d.demographic_no = STAT.demographic_no) -- TOTAL HBA1C LABS ISSUED - CONSIDERS PATIENTS WITH SEVERAL LABS LEFT JOIN ( SELECT COUNT(*) AS reqnumber, demographic_no FROM formLabReq10 WHERE b_hba1c = 1 AND DATE(formCreated) BETWEEN DATE("{FROM}") AND DATE("{TO}") AND demographic_no > 0 GROUP BY demographic_no HAVING COUNT(demographic_no) > -1 ) REQ ON ( d.demographic_no = REQ.demographic_no ) WHERE d.patient_status LIKE "%AC%" AND dxr.coding_system LIKE "icd9" AND dxr.dxresearch_code LIKE "250" AND dxr.status NOT LIKE "%D%" AND d.demographic_no > 0 ) fin;