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: Insurance Time to Pay

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 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.

Code

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