Retention Measure 1: Gap Measure

eCW in+care Measures

SQL Code

Retention Measure 1: Gap Measure

MEASURE 1 DENOMINATOR

SET SESSION wait_timeout = 999999999999;

SET @START2 = '2010-10-01'; /*The start date for the denominator measurement period*/

SET @END2 = '2011-03-31'; /*The end date for the denominator measurement period*/

SELECT count(distinct e.patientid)

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND e.patientid = p.patientid

AND u.uid = pt.pid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start2 AND @end2

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'STD%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK';

MEASURE 1 NUMERATOR

SET SESSION wait_timeout = 999999999999;

SET @START2 = '2010-10-01'; /*The start date for the denominator measurement period*/

SET @END2 = '2011-03-31'; /*The end date for the denominator measurement period*/

SET @START3 = '2011-04-01'; /*The start date for the numerator measurement period*/

SET @END3 = '2011-09-30'; /*The start date for the numerator measurement period*/

SELECT count(distinct e.patientid)

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start2 AND @end2

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK'

AND e.patientid not in (SELECT distinct e.patientid

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start3 AND @end3

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK');

Retention Measure 2: Visit Frequency Measure

MEASURE 2 DENOMINATOR

SET SESSION wait_timeout = 999999999999;

SET @START = '2009-10-01'; /*The start date for the denominator measurement period*/

SET @END = '2011-03-31'; /*The end date for the denominator measurement period*/

SELECT count(distinct e.patientid)

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND e.patientid = p.patientid

AND u.uid = pt.pid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start AND @end

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK';

MEASURE 2 NUMERATOR

Step1:- Identify the patients having appointments in each 6 month period of measurement period (10/2009-9/2010)

SET @START4 = '2009-10-01'; /*Beginning of first 6 month interval of four*/

SET @END4 = '2010-03-31'; /*Ending of first 6 month interval of four*/

SET @START3 = '2010-04-01'; /*Beginning of second 6 month interval of four*/

SET @END3 = '2010-09-31'; /*Ending of second 6 month interval of four*/

SELECT DISTINCT CONCAT(e.patientid,',')

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND e.deleteflag = 0

AND e.date BETWEEN @start3 AND @end3

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK'

AND e.patientid IN (SELECT DISTINCT e.patientid

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND e.deleteflag = 0

AND e.date BETWEEN @start4 AND @end4

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK');

Step2:- # of patients having appointments in each 6 month period of measurement period (10/2010-9/2011)

SET SESSION wait_timeout = 9999999999;

SET @START2 = '2010-10-01'; /*Beginning of third 6 month interval of four*/

SET @END2 = '2011-03-31'; /*Ending of third 6 month interval of four*/

SET @START1 = '2011-04-01'; /*Beginning of fourth 6 month interval of four*/

SET @END1 = '2011-09-31'; /*Ending of fourth 6 month interval of four*/

SELECT COUNT(DISTINCT e.patientid)

FROM enc e, users u, problemlistp, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND e.deleteflag = 0

AND e.date BETWEEN @start1 AND @end1

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK'

AND e.patientid IN (SELECT DISTINCT e.patientid

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND e.deleteflag = 0

AND e.date BETWEEN @start2 AND @end2

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visit types used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK')

AND e.patientid IN (Paste the result/patientid’s from Numerator Step1 here);

Retention Measure 3: New Patients Measure

MEASURE 3DENOMINATOR

SET SESSION wait_timeout = 9999999999;

SET @START = '2010-10-01'; /*Beginning of denominator measurement period*/

SET @END = '2011-01-31'; /*Ending of numerator measurement period*/

SELECT DISTINCT CONCAT(e.patientid,',')

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND e.patientid = p.patientid

AND u.uid = pt.pid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start AND @end

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT-ne%' /*Newly enrolled patients based on visit types*/

OR e.visittype LIKE 'ANOSC-ne%'

OR e.visittype LIKE 'COLPOS-ne%'

OR e.visittype LIKE 'ENDO-ne%'

OR e.visittype LIKE 'GYN-ne%'

OR e.visittype LIKE 'HIVAID-ne%'

OR e.visittype LIKE 'OB-ne%'

OR e.visittype LIKE 'tran-fm-ne%'

OR e.visittype LIKE 'TRAN-mf-ne%')

AND e.status = 'CHK';

MEASURE 3NUMERATOR

Step1:- Identify patients from denominator who had an appointment in the second 4 month interval

SET SESSION wait_timeout = 9999999999;

SET @start1 = '2011-02-01'; /*Beginning of second 4 month interval of three*/

SET @end1 = '2011-05-31'; /*Ending of second 4 month interval of three*/

SELECT DISTINCT CONCAT(e.patientid,',')

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start1 AND @end1

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visittypes used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittypeLIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK'

AND e.patientid IN (Paste results/patientid from Denominator);

Step 2:- Identify patients from step one who also had an appointment in the third 4 month interval

SET SESSION wait_timeout = 9999999999;

SET @start2 = '2011-06-01'; /*Beginning of third 4 month interval of three*/

SET @end2 = '2011-09-30'; /*Ending of third 4 month interval of three*/

SELECT COUNT(DISTINCT e.patientid)

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND u.uid = pt.pid

AND e.patientid = p.patientid

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND e.deleteflag = 0

AND e.date BETWEEN @start2 AND @end2

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visittypes used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

OR e.visittype LIKE 'TRAN%')

AND e.status = 'CHK'

AND e.patientid IN (Paste results/patientid from Numerator Step1 here);

Retention Measure 4: Viral Suppression Measure

MEASURE 4DENOMINATOR

SET SESSION wait_timeout = 9999999999;

SET @start = '2010-10-01'; /*Beginning of denominator measurement period*/

SET @end = '2011-09-31'; /*Ending of denominator measurement period*/

SELECT DISTINCT CONCAT(e.patientid,',')

FROM enc e, users u, problemlist p, patients pt

WHERE e.patientid = u.uid

AND E.patientid = p.patientid

AND u.uid = pt.pid

AND pt.deceased = 0

AND u.delflag = 0

AND u.status = 0

AND p.asmtid IN (SELECT itemid FROM itemdetail WHERE VALUE IN ('042','V08'))

AND e.deleteflag = 0

AND e.date BETWEEN @start1 AND @end1

AND u.ulname > 'Test'

AND (e.visittype LIKE 'ADULT%' /*the medical visittypes used at the health center*/

OR e.visittype LIKE 'ANN%'

OR e.visittype LIKE 'ANOSC%'

OR e.visittype LIKE 'COLPOS%'

OR e.visittype LIKE 'CON%'

OR e.visittype LIKE 'ENDO%'

OR e.visittype LIKE 'EOB%'

OR e.visittype LIKE 'EST%'

OR e.visittype LIKE 'F/U%'

OR e.visittype LIKE 'GYN%'

OR e.visittype LIKE 'HEPATIT%'

OR e.visittype LIKE 'HIV-RAPID%'

OR e.visittype LIKE 'HIVAID%'

OR e.visittype LIKE 'NP%'

OR e.visittype LIKE 'OB%'

OR e.visittype LIKE 'OV%'

OR e.visittype LIKE 'PEDS%'

OR e.visittype LIKE 'PULM%'

ORe.visittype LIKE 'TRAN%')

AND e.status = 'CHK';

MEASURE 4NUMERATOR

SET SESSION wait_timeout = 9999999999;

SET @start = '2010-10-01'; /*Start date for numerator measurement period*/

SET @end = '2011-09-31'; /*End date for numerator measurement period*/

SELECT COUNT(DISTINCT e2.patientid,',')

FROM enc e2, users u2, labdata ld2, labdatadetailldd

WHERE e2.patientid = u2.uid

AND e2.encounterid = ld2.encounterid

AND ld2.reportid = ldd.reportid

AND ld2.itemid IN (select itemid from items where item name LIKE ‘%Viral Load%’) /*Depends on the name of your VL Lab in eCW*/

AND ldd.propidIN (select itemid from items where item name LIKE ‘%Viral Load%’) /*Depends on the name of your VL Lab in eCW*/

AND e2.encounterid =(SELECT MAX(ld2.encounterid))

AND ldd.value < 200

AND ld2.deleteflag = 0

AND e2.deleteflag = 0

AND e2.date BETWEEN @start AND @end

AND e2.status = 'CHK'

AND u2.ulname > 'Test'

AND u2.uid IN (Paste Results/Patientid’sfrom Denominator);

Andy Doy1December 8, 2011