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:
- 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 A1) 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.
To highlight and copy the code below to your clipboard, simply click the Copy button.
with t as (select * from ( select c.invoiceno as claimno, c.claimdate, c.ins_carrier_code_1, c.patno, p.final_paid_date, s.last_sent, p.final_paid_date - s.last_sent as elapsed_time from ( select * from claims1 where ins_balance = 0 and claimdate between :Startdate and :enddate and charge >= 0.01 and patno >99 ) c left outer join ( select invoiceno, max(date1) as final_paid_date from archive_Transactions where cptcode = '1' and archive_flag = 1 group by invoiceno) p on p.invoiceno = c.invoiceno left outer join (select invoiceno, max(asofdate) as last_sent from claim_status where hl7_category in ('Q2','A1') group by invoiceno) s on s.invoiceno = c.invoiceno ) where elapsed_time is not null) select avg(elapsed_time) as avg_elapsed_time, ins_carrier_code_1 from t group by ins_carrier_code_1