SQL: HEDIS-like: ADHD Followup

There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator.

About

This follows the HEDIS conventions for the ADHD initiation and followup metrics.  
If you are not familiar with how this metric is computed, read this first: Follow-Up Care for Children Prescribed ADHD Medication (ADD) | eCQI Resource Center

Please note that this SQL provides a good approximation of how insurance companies compute your ADD followup scores based on claims data. However, this SQL does NOT give the complete, nuanced, highly prescribed results that are required by the PCMH 2024 Quality Reports eCQMs. That report is available through our PCMH Reports Bundle.

A sample image of this SQL report run in the Database Viewer is shown below:


Caveats

  • This is a large, time-consuming report - we recommend not running it for more than a year when prompted for dates.
  • If the end date of your report is less than 9 months ago, your 8-month followup numbers will be artificially low.
  • At run time, you will be prompted for "PROVIDER_ID"  - this is the OP ID of the clinician you are looking for.  This clinician is the prescriber of the medication (who may or may not be the same clinician who saw the patient in followup.)
  • Numerator 1 is whether this patient episode passes or fails the 30 day followup.  Numerator 2 is whether it passes or fails the 2-9 month followup.
  • For most practices, most prescriptions will show "Exclude-Med Hx" in the denominator.  This is because most ADHD Rxs we write are for established dxs, not new ones.  You can filter those out; they appear in the SQL so you can see the "guts" of the computation.
  • A "visit" is defined as a sick or well note in their chart.  It does not include patient messages or other parts of the OP chart.  I you are using encounter notes for something that did not reflect a face-to-face visit with the patient (e.g. patient termination letters or to document no-shows), your numerators will be artificially inflated.

SQL Code: Firebird

To highlight and copy the code below to your clipboard, simply click the Copy button.

with rxs as 
(select distinct patno, date1 as meddate, P_addr_id, datediff(day,:start_date,date1 ) as day0 from medications m where m.date1 between :start_date - 120 and :end_date + 30 and purpose = 'Med - to dispense' and
(
m.rxcui in ("1009145","1091133","1091137","1091145","1091150","1091152","1091155","1091161","1091170","1091176","1091185","1091191","1091202","1091210","1091218","1091225","1091322","1091341","1091389","1091392","1091497","1251820","349591","349592","349593","349594","349595","410062","541363","541878","541892","577957","577961","608139","608143","687043","753436","753438","753440","753441","854830","854834","854838","854842","854846","854850","861221","861223","861225","861227","861232","861237","884386","884520","884522","884532","884535","884660","884684","899439","899461","899485","899495","899511","899518","899548","899557","977844","977851","977857","977860","1009147","1091135","1091139","1091143","1091153","1091157","1091163","1091167","1091172","1091178","1091182","1091187","1091193","1091197","1091204","1091212","1091220","1091226","1091324","1091343","1091379","1091391","1091395","1091488","1091500","1425339","1535451","1600909","1648180","1648189","1648192","1648195","1648198","1648201","352317","352318","352319","352320","352321","541365","541879","541894","577960","577962","617945","617947","687045","753437","753439","753442","753443","854832","854836","854840","854844","854848","854852","861222","861224","861226","861228","861233","861238","884526","884528","884534","884537","884538","899441","899463","899487","899497","899513","899519","899549","899559","977861")
) order by patno, meddate
), excl as
(select patno, min(day0) as first_day, case when min(day0) <0 then 'Exclude - Med Hx' else 'Include' end as denom from rxs group by patno),
visits as
(select patno, date1 as enc_date from enc_note where date1 between :Start_date +1 and :end_date +300  union select patno, date1 as enc_date from physical where date1 between :Start_date +1 and :end_Date +300) ,
conglom as 
(select excl.*, meddate, meddate+30 as first_metric, meddate + 210 as second_metric from excl
inner join rxs on excl.patno = rxs.patno and first_day =  datediff(day,:start_date,meddate )
), classif as 
(
select conglom.*, enc_date, case when enc_date <= first_metric then 'First' when enc_date <= second_metric then 'Second' else '' end as counted from conglom
left outer join visits on visits.patno = conglom.patno and enc_date>meddate and enc_date<=second_metric
),
rxer as 
(
select r1.patno, r1.mini,  r2.p_addr_id as prescriber from
 (select patno, min(meddate) as mini from rxs group by patno)
r1
inner join rxs r2 on r1.patno = r2.patno and r2.meddate = r1.mini
)

select patno, meddate as episode_date, prescriber, loc_name, denom, case when denom like '%Exclude%' then '-' when n1 >=1 then 'Pass' when n1 is null then 'Fail' else 'error' end as numerator_1,
 case when denom like '%Exclude%' then '-' when n2 >=2 then 'Pass' when n2 is null then 'Fail' when n2 = 1 then 'Fail' else 'error' end as numerator_2 from
(
select distinct classif.patno, meddate, denom, n1, n2 from classif
inner join (select distinct patno, denom from excl) a on a.patno = classif.patno
left outer join (select patno, count(counted) as n1 from classif where counted = 'First' group by patno) m1 on m1.patno = classif.patno
left outer join (select patno, count(counted) as n2 from classif where counted = 'Second' group by patno) m2 on m2.patno = classif.patno
) c
inner join rxer on rxer.patno = c.patno
inner join register on register.patno = c.patno
inner join location on location.id = register.loc_id
where prescriber = :prescriber_id
and datediff(day,birthdat,meddate) between 2191 and 4748


SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

select c.patno, meddate as episode_date, prescriber, loc_name, denom, case when denom like '%Exclude%' then '-' when n1 >=1 then 'Pass' when n1 is null then 'Fail' else 'error' end as numerator_1,
 case when denom like '%Exclude%' then '-' when n2 >=2 then 'Pass' when n2 is null then 'Fail' when n2 = 1 then 'Fail' else 'error' end as numerator_2 from
(
select distinct classif.patno, meddate, classif.denom, n1, n2 from 
( select conglom.*, enc_date, case when enc_date <= first_metric then 'First' when enc_date <= second_metric then 'Second' else '' end as counted from  
(select excl.*, meddate, date_add(meddate, interval 30 day) as first_metric, date_add(meddate, interval 210 day) as second_metric 
from (select patno, min(day0) as first_day, case when min(day0) <0 then 'Exclude - Med Hx' else 'Include' end as denom from (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
(
m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs group by patno) excl
inner join (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
( m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs on excl.patno = rxs.patno and first_day =  timestampdiff(day,cast(:start_date as date),meddate )
) conglom
left outer join (select patno, date1 as enc_date from enc_note where date1 between date_add(cast (:start_date as date),interval 1 day)  and   date_add(cast(:end_date as date),interval 300 day)  union select patno, date1 as enc_date from physical where date1 between date_add(cast (:start_date as date),interval 1 day)  
and   date_add(cast(:end_date as date),interval 300 day) ) visits on visits.patno = conglom.patno and enc_date>meddate and enc_date<=second_metric 
) classif
inner join (select distinct patno, denom from (select patno, min(day0) as first_day, case when min(day0) <0 then 'Exclude - Med Hx' else 'Include' end as denom from (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and ( m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs group by patno) excl) a on a.patno = classif.patno
left outer join (select patno, count(counted) as n1 from ( select conglom.*, enc_date, case when enc_date <= first_metric then 'First' when enc_date <= second_metric then 'Second' else '' end as counted from  
(select excl.*, meddate, date_add(meddate, interval 30 day) as first_metric, date_add(meddate, interval 210 day) as second_metric 
from (select patno, min(day0) as first_day, case when min(day0) <0 then 'Exclude - Med Hx' else 'Include' end as denom from (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
(
m.rxcui in 
('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs group by patno) excl
inner join (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
( m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs on excl.patno = rxs.patno and first_day =  timestampdiff(day,cast(:start_date as date),meddate )
) conglom
left outer join (select patno, date1 as enc_date from enc_note where date1 between date_add(cast (:start_date as date),interval 1 day)  and   date_add(cast(:end_date as date),interval 300 day)  union select patno, date1 as enc_date from physical where date1 between date_add(cast (:start_date as date),interval 1 day)  
and   date_add(cast(:end_date as date),interval 300 day) ) visits on visits.patno = conglom.patno and enc_date>meddate and enc_date<=second_metric 
) classif where counted = 'First' group by patno) m1 on m1.patno = classif.patno
left outer join (select patno, count(counted) as n2 from ( select conglom.*, enc_date, case when enc_date <= first_metric then 'First' when enc_date <= second_metric then 'Second' else '' end as counted from  
(select excl.*, meddate, date_add(meddate, interval 30 day) as first_metric, date_add(meddate, interval 210 day) as second_metric 
from (select patno, min(day0) as first_day, case when min(day0) <0 then 'Exclude - Med Hx' else 'Include' end as denom from (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
(
m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs group by patno) excl
inner join (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
( m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs on excl.patno = rxs.patno and first_day =  timestampdiff(day,cast(:start_date as date),meddate )
) conglom
left outer join (select patno, date1 as enc_date from enc_note where date1 between date_add(cast (:start_date as date),interval 1 day)  and   date_add(cast(:end_date as date),interval 300 day)  union select patno, date1 as enc_date from physical where date1 between date_add(cast (:start_date as date),interval 1 day)  
and   date_add(cast(:end_date as date),interval 300 day) ) visits on visits.patno = conglom.patno and enc_date>meddate and enc_date<=second_metric 
) classif where counted = 'Second' group by patno) m2 on m2.patno = classif.patno
) c

inner join ( select r1.patno, r1.mini,  r2.p_addr_id as prescriber from
 (select patno, min(meddate) as mini from (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
(
m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) rxs group by patno)
r1 inner join (select distinct patno, date1 as meddate, P_addr_id, timestampdiff(day,cast (:start_date as date),date1 ) as day0 from medications m where m.date1 between date_add(cast (:start_date as date),interval -120 day) and date_add(cast(:end_date as date),interval 30 day) 
 and  purpose = 'Med - to dispense' and
(
m.rxcui in ('1009145','1091133','1091137','1091145','1091150','1091152','1091155','1091161','1091170','1091176','1091185','1091191','1091202','1091210','1091218','1091225','1091322','1091341','1091389','1091392','1091497','1251820','349591','349592','349593','349594','349595','410062','541363','541878','541892','577957','577961','608139','608143','687043','753436','753438','753440','753441','854830','854834','854838','854842','854846','854850','861221','861223','861225','861227','861232','861237','884386','884520','884522','884532','884535','884660','884684','899439','899461','899485','899495','899511','899518','899548','899557','977844','977851','977857','977860','1009147','1091135','1091139','1091143','1091153','1091157','1091163','1091167','1091172','1091178','1091182','1091187','1091193','1091197','1091204','1091212','1091220','1091226','1091324','1091343','1091379','1091391','1091395','1091488','1091500','1425339','1535451','1600909','1648180','1648189','1648192','1648195','1648198','1648201','352317','352318','352319','352320','352321','541365','541879','541894','577960','577962','617945','617947','687045','753437','753439','753442','753443','854832','854836','854840','854844','854848','854852','861222','861224','861226','861228','861233','861238','884526','884528','884534','884537','884538','899441','899463','899487','899497','899513','899519','899549','899559','977861')
) order by patno, meddate
) r2 on r1.patno = r2.patno and r2.meddate = r1.mini)
 rxer on rxer.patno = c.patno
inner join register on register.patno = c.patno
inner join location on location.id = register.loc_id
where prescriber = :prescriber_id
and timestampdiff(year,birthdat,meddate) between 6 and 12