Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: BMI and ZCode for All Active Patients

About

This report shows all active patients who have ever had a BMI computed. It lists the most recent date of BMI computation, age at computation, BMI, BMI percentile (for under 18 years old), and appropriate ICD-10 Z code for that BMI.

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

Caveats

  • Age is the patient’s age when the BMI was last computed, not the current age.
  • Patients who have never had a BMI computed do not appear in the report.

Code

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

select patno, birthdat, date1, datediff(month,birthdat,date1)/12 as age, bmi, bmi_percent, 
case when  datediff(month,birthdat,date1)/12 < 18 then 
(case when bmi_percent <5 then 'Z68.51' when bmi_percent <85 then 'Z68.52' when bmi_percent <95 then
'Z68.53' else 'Z68.54' end) else (case when bmi<40  then ( 'Z68.' ||  substring(bmi from 1 for 2)) when bmi <45 then 'Z68.41' when bmi <50 then
'Z68.42' when bmi <60 then 'Z68.43' when bmi <70 then 'Z68.44' else 'Z68.45' end) end as zcode from
(
select birthdat, patno, physical_chart.date1, physical_chart.bmi, physical_chart.bmi_percent  from register
inner join 
( select patno, max(date1) as most_recent from
physical_chart where bmi <>0 group by patno ) p
on p.patno = register.patno 
inner join physical_chart on register.patno = physical_chart.patno and p.most_recent= physical_chart.date1
where patno >=101 and status_pat = 'ACTIVE' and lname <> 'TESTPATIENT'
and bmi >0
)bmi