About

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

Caveats

Read all of the below before running the SQL.

• Use a 6-12 month period ending with the current day when prompted to enter the begin and end dates. Larger practices should use a smaller date range.
• This report will take a long time to run. You may consider using the Extract option to save the report to a file on your computer.
• See below for an explanation of each column using the above image as an example.
  • YW: This is the year-week of the year, for example, 2020-01 if the first week of 2020 and 2019-52 is the last week of 2019.
  • TOTAL_CHARGES: This is the total amount charged for dates of service in the YW column.
  • TOTAL_PAID-TO_DATE: The column will display the amount you have been paid, so far, for the work you did with DOS week YW. For example, using the above image, the practice charged $41,889.33 the first week in January 2020. The practice was paid $18.303.06 at some point thereafter. This is not the same as what was posted during the week YW, which would have been mostly work done in December.
  • TYP: This column will display the typical collections ration computed for the period you have run the report, minus the last 120 days. This would be total paid to date / total charges.
  • EXPECTED_PAYMENT: This column will display the TOTAL_CHARGES  times the TYP. In other words, if you were paid at your typical collections ratio for those charges, what would you have been paid?  This number is not going to be precise with TOTAL_PAID.  TOTAL_PAID shows what you actually collected for that work.  EXPECTED_PAYMENT shows an estimate based on prior performance. The more recent you are in time, i.e. if 120 days have not passed yet, the more discrepant these two numbers are. For example, you can see that for the weeks of Janaury, this practice has collected about $1000.00 below the estimated week; this is usually late insurance payments or corrections or late patient payments coming in.  The more recent weeks, e.g. March weeks, are substantially lower, because many of those claims have not yet processed. The EXPECTED_PAYMENT column is what the practice can reasonable expect to be paid based on their charge work for every week of the year.

SQL Code: Firebird

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

Copy

with overview as (
select patno, trnsxno, date1, charge, p,  (extract(year from date1) || '-' || lpad(extract(week from date1),2,0)) as yw from archive_transactions at1
left outer join (select txnopaid, sum(payment + copayrecd) as p from archive_transactions where date1>= :Start_date group by txnopaid) at2 on at2.txnopaid = at1.trnsxno
 where at1.date1 between :Start_date and :end_date and at1.txnopaid = 0
),
tpc as
(select  round(sum(p)/sum(charge),2) as typ from overview where date1 <= :end_date - 120)
select total_charge, total_paid_to_date, typ, round( total_charge * (select typ from tpc) ,2) as expected_payment, yw from (
select round(sum(charge),2) as total_charge, round(sum(p),2) as total_paid_to_date, (select typ from tpc) as typ,  yw from overview group by yw
)

SQL Code: MySQL

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

Copy

select total_charge, total_paid_to_date, typ, round( total_charge * (select typ from 
(select  round(sum(p)/sum(charge),2) as typ from (
select patno, trnsxno, date1, charge, p,  (extract(year from date1) || '-' || lpad(extract(week from date1),2,0)) as yw from archive_transactions at1
left outer join (select txnopaid, sum(payment + copayrecd) as p from archive_transactions where date1>= :start_date group by txnopaid) at2 on at2.txnopaid = at1.trnsxno
 where at1.date1 between :start_date and :end_date and at1.txnopaid = 0
) overview where date1 <= date_add(cast(:end_date as date),interval -120 day) )
tpc) ,2) as expected_payment, yw from (
select round(sum(charge),2) as total_charge, round(sum(p),2) as total_paid_to_date, (select typ from 
(select  round(sum(p)/sum(charge),2) as typ from (
select patno, trnsxno, date1, charge, p,  (extract(year from date1) || '-' || lpad(extract(week from date1),2,0)) as yw from archive_transactions at1
left outer join (select txnopaid, sum(payment + copayrecd) as p from archive_transactions where date1>= :start_date group by txnopaid) at2 on at2.txnopaid = at1.trnsxno
 where at1.date1 between :start_date and :end_date and at1.txnopaid = 0
) overview where date1 <= date_add(cast(:end_date as date),interval -120 day) )
tpc) as typ,  yw from
(
select patno, trnsxno, date1, charge, p,  (extract(year from date1) || '-' || lpad(extract(week from date1),2,0)) as yw from archive_transactions at1
left outer join (select txnopaid, sum(payment + copayrecd) as p from archive_transactions where date1>= :start_date group by txnopaid) at2 on at2.txnopaid = at1.trnsxno
 where at1.date1 between :start_date and :end_date and at1.txnopaid = 0
)
 overview group by yw
)t1