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;