| 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 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.
SQL Code: Firebird
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.date1SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct pa.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, cast(recent_pmt as date), last_paid from (select register.guarantor_id, register.patno, fname, lname, copaybal as pat_balance, coalesce(pat_resp_date,claimdate) as aging_date, timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) 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 ) pa left outer join ( select patno, sum(patient_balance) as curr_bal from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=30 group by patno ) c_bal on pa.patno = c_bal.patno left outer join ( select patno, sum(patient_balance) as bal31 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=60 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>30 group by patno ) bal31_60 on pa.patno = bal31_60.patno left outer join ( select patno, sum(patient_balance) as bal61 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=90 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>61 group by patno ) bal61_90 on pa.patno = bal61_90.patno left outer join ( select patno, sum(patient_balance) as bal91 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=120 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>91 group by patno ) bal91_120 on pa.patno = bal91_120.patno left outer join ( select patno, sum(patient_balance) as bal121 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=150 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>121 group by patno ) bal121_150 on pa.patno = bal121_150.patno left outer join ( select patno, sum(patient_balance) as bal151 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=180 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>151 group by patno ) bal151_180 on pa.patno = bal151_180.patno left outer join ( select patno, sum(patient_balance) as bal181 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) <=365 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate())>181 group by patno ) bal181_365 on pa.patno = bal181_365.patno left outer join ( select patno, sum(patient_balance) as bal365 from claims1 where patient_balance >= 0.01 and timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) >365 group by patno ) 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 (select guarantor_id, register.patno, fname, lname, copaybal as pat_balance, coalesce(pat_resp_date,claimdate) as aging_date, timestampdiff(day, coalesce(pat_resp_date,claimdate),curdate()) 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 ) 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
