We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (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 Responsibility Aging

About

This report shows all patients that are aged by patient responsibility date.  They are grouped by guarantors.  It also gives a family total balance, the date, and amount (if any) of the last patient payment on the account. The Patient Responsibility Date is defined as the date in which the balance was passed to the patient.

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

Caveats

None.

Code

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

with pa as 
(select guarantor_id, patno, fname, lname, copaybal as pat_balance,  coalesce(pat_resp_date,claimdate) as aging_date, ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate)) as aged_days,  patient_balance, contact.home_phone, contact.cell_phone, contact.work_phone
 from register
inner join contact on contact.id = register.guarantor_id
left outer join (select patno, claimdate, patient_balance,pat_resp_date from claims1 where patient_balance >= 0.01) c on c.patno = register.patno
 where copaybal >= 0.01 ),
c_bal as 
(
select patno, sum(patient_balance) as curr_bal from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=30 group by patno
),
bal31_60 as 
(
select patno, sum(patient_balance) as bal31 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=60 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>30  group by patno
),
bal61_90 as 
(
select patno, sum(patient_balance) as bal61 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=90 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>61  group by patno
),
bal91_120 as 
(
select patno, sum(patient_balance) as bal91 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=120 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>91  group by patno
),
bal121_150 as 
(
select patno, sum(patient_balance) as bal121 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=150 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>121  group by patno
),
bal151_180 as 
(
select patno, sum(patient_balance) as bal151 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=180 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>151  group by patno
),
bal181_365 as 
(
select patno, sum(patient_balance) as bal181 from claims1 where patient_balance >= 0.01 and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))  <=365  and  ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>181  group by patno
),
bal365 as 
(
select patno, sum(patient_balance) as bal365 from claims1 where patient_balance >= 0.01 and   ceiling(cast('today' as date) - coalesce(pat_resp_date,claimdate))>365  group by patno
)

select distinct guarantor_id as guarantor_id, first_name as guarantor_first, last_name as guarantr_last,  pa.patno as patient_chart, fname as patient_first, lname as patient_last, curr_bal,bal31,bal61,bal91,bal121,bal151,bal181,bal365,pat_balance, family_total, recent_pmt, last_paid from pa
left outer join c_bal on pa.patno = c_bal.patno
left outer join bal31_60 on pa.patno = bal31_60.patno
left outer join bal61_90 on pa.patno = bal61_90.patno
left outer join bal91_120 on pa.patno = bal91_120.patno
left outer join bal121_150 on pa.patno = bal121_150.patno
left outer join bal151_180 on pa.patno = bal151_180.patno
left outer join bal181_365 on pa.patno = bal181_365.patno
left outer join bal365 on pa.patno = bal365.patno
left outer join contact on contact.id = pa.guarantor_id
left outer join (select sum(patient_balance) as family_total, guarantor_id from pa group by guarantor_id) t on t.guarantor_id = pa.guarantor_id
left outer join (select patno, max(date1) as recent_pmt from patient_credit_acct where pat_amount >= 0.01 group by patno) pca1 on pca1.patno = pa.patno
left outer join (select patno, date1, sum(pat_amount) as last_paid from patient_credit_acct where pat_amount >= 0.01 group by patno, date1) pca2 on pca1.patno = pca2.patno and pca1.recent_pmt = pca2.date1