About

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.

SQL Code: Firebird

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

Copy

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

SQL Code: MySQL

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

Copy

select avg(elapsed_time) as avg_elapsed_time, ins_carrier_code_1  from 
(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 :start_date and :end_date  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
) t1
where elapsed_time is not null) r

 group by ins_carrier_code_1