| 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 provides data for an average to “time to paid claim” in days by insurance. This report is based on the date range of claims that you specify.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This report looks for claims with no insurance balance (i.e. fully paid/adjudicated by insurance) which were created (i.e. converted from superbills) in the date range you specify.
- The “elapsed time” is computed by taking the difference between the last time the claim was sent to insurance (based on the most recent date attached to the claim status codes of Q2) and the most recent/last payment date on the claim (i.e. the date on which the claim finally received full payment).
- If you have a large practice, running this report for periods of more than a week at a time has the potential to be very slow or crash your Database Viewer. Start with a date range of a few days, then gradually expand it.
Report Usage Ideas
Look at your outliers over multiple date ranges. If any of your top insurances are consistently taking a relatively long time to pay, you should revisit your billing practices and contracts with that carrier.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
with sick as ( select patno, date1, cptcode, rend_addr_id from archive_transactions where archive_flag <=1 and (cptcode between '99212' and '99215' or cptcode between '99201' and '99205') and date1 between :Start_date and :end_date), well as (select patno, date1, cptcode from archive_transactions where archive_flag <=1 and (cptcode between '99381' and '99385' or cptcode between '99391' and '99395') and date1 between :Start_date and :end_date) select sick.patno, sick.date1, staffname as rendering, sick.cptcode as sick_cpt, well.cptcode as well_cpt from sick inner join well on well.patno = sick.patno and well.date1 = sick.date1 left outer join staff1 on staff1.staffid = sick.rend_addr_id
SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
with sick as ( select patno, date1, cptcode, rend_addr_id from archive_transactions where archive_flag <=1 and (cptcode between '99212' and '99215' or cptcode between '99201' and '99205') and date1 between :Start_date and :end_date), well as (select patno, date1, cptcode from archive_transactions where archive_flag <=1 and (cptcode between '99381' and '99385' or cptcode between '99391' and '99395') and date1 between :Start_date and :end_date) select sick.patno, sick.date1, staffname as rendering, sick.cptcode as sick_cpt, well.cptcode as well_cpt from sick inner join well on well.patno = sick.patno and well.date1 = sick.date1 left outer join staff1 on staff1.staffid = sick.rend_addr_id
