Peter Hutten-Czapski 2017
CDM
Diabetes
Diabetes with ACR in range
% of patients with diabetes aged 40 years and older who have had ACR tests outside of range within the past 24 months
PHC
1
This is a test template for the Diabetes with ACR Testing Indicator query
11-07-2017
SELECT
IF ( COUNT(fin.patient) > 0,
SUM( CASE WHEN fin.acr BETWEEN ${lowerLimit.acr} AND ${upperLimit.acr} THEN 1 ELSE 0 END )
, 0) AS "0% < ACR <= 2.8%",
IF ( COUNT(fin.patient) > 0,
SUM( CASE WHEN fin.acr > ${upperLimit.acr} THEN 1 ELSE 0 END )
, 0) AS "ACR > 2.8%"
FROM (
SELECT
d.demographic_no AS patient,
ACR.dataField AS acr
FROM demographic d
INNER JOIN dxresearch dxr
ON ( d.demographic_no = dxr.demographic_no)
-- ACR measurement
LEFT JOIN (
SELECT m.dataField, m.demographicNo
FROM measurements m
JOIN (
SELECT "ACR" AS 'name'
) mm
ON (mm.`name` = m.type)
WHERE DATE(m.dateObserved) > ${lowerLimit.date}
AND m.demographicNo > 0
GROUP BY m.demographicNo HAVING COUNT(m.demographicNo) > -1
) ACR
ON (d.demographic_no = ACR.demographicNo)
WHERE d.patient_status LIKE ${active}
AND dxr.dxresearch_code IN ${dxCodes}
AND dxr.`status` NOT LIKE "%D%"
AND d.demographic_no > 0
AND d.provider_no LIKE ${provider}
) fin;
09-11-2017
SELECT *
FROM demographic d
INNER JOIN dxresearch dxr
ON ( d.demographic_no = dxr.demographic_no)
LEFT JOIN (
SELECT m1.dataField, m1.demographicNo, m1.dateObserved
FROM measurements m1
INNER JOIN (
SELECT MAX(id) AS id
FROM measurements m
JOIN (
SELECT "ACR" AS 'name'
) mm
ON (mm.`name` = m.type)
WHERE DATE(m.dateObserved) > ${lowerLimit.date}
AND m.demographicNo > 0
GROUP BY m.demographicNo
) m2
ON (m1.id = m2.id)
) ACR
ON (d.demographic_no = ACR.demographicNo)
WHERE d.patient_status LIKE ${active}
AND dxr.dxresearch_code IN ${dxCodes}
AND dxr.`status` NOT LIKE "%D%"
AND d.demographic_no > 0
AND d.provider_no LIKE ${provider}
false