We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s official version is 14.19.1, which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: Patient Balances, Aged with Patient Status, Guarantor, PCP, and Next Appt

About

This report shows all patients with a balance, aged into 0-30, 31-60, 61-90, 91-120, and 121+ day buckets.  This report also shows guarantor ID and name, patient status PCP, and the next appointment scheduled (if applicable).

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

Caveats

  • “Aging” is defined as how many days between run date and patient responsibility date.  Patient responsibility date is defined as the date on which the insurance company adjudicated the claim and sent a balance to patient responsibility.
  • This report may take 30-45 seconds to execute if you have a large practice and/or many open balances.

Code

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

with b as 
(

select guarantor_id, first_name as guarantor_first, last_name as guarantor_last, patno as chart, fname as patient_First, lname as patient_last, staffname as pcp,  copaybalance, status_pat, effective_dor,
 datediff(day, cast(effective_dor as date), cast('today' as date)) as elapsed, case when  datediff(day, cast(effective_dor as date), cast('today' as date)) <31 then '0-30' when  datediff(day, cast(effective_dor as date), cast('today' as date)) <61 then '31-60' when  datediff(day, cast(effective_dor as date), cast('today' as date)) < 91 then '61-90' when  datediff(day, cast(effective_dor as date), cast('today' as date)) < 121 then '91-120' else '121+' end as buckets
from
(

select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, guarantor_id, staffname, first_name, last_name, status_pat from
 (select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from archive_Transactions where archive_flag = 1 and cptcode not in ('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
INNER JOIN STAFF1 on staff1.staffid = register.addr_id
inner join contact on contact.id = register.guarantor_id
order by patno
) 
)


select distinct guarantor_id, guarantor_first, guarantor_last, b.chart, patient_first, patient_last, pcp , status_pat,
a1.b1 as B0_30, a2.b2 as B31_60, a3.b3 as B61_90, a4.b4 as B91_120, a5.b5 AS B121, next_appt
from b
left outer join (select coalesce(sum(copaybalance),0) as b1, chart from b where buckets='0-30' group by chart) a1 on a1.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b2, chart from b where buckets='31-60' group by chart) a2 on a2.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b3, chart from b where buckets='61-90' group by chart) a3 on a3.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b4, chart from b where buckets='91-120' group by chart) a4 on a4.chart = b.chart
left outer join (select coalesce(sum(copaybalance),0) as b5, chart from b where buckets='121+' group by chart) a5 on a5.chart = b.chart
left outer join (select patno, min(appt_Date) as next_appt from schedule where visit_status is null and appt_date >= cast('today' as date) group by patno) s on s.patno = b.chart