This report looks at the number of claims per calendar month (for a date range you specify) that were completely written off on the insurance-side. This is a good general monitor for performance around timely filing and appeals for your billing department.
A sample image of this SQL report run in the Database Viewer is shown below:
This SQL looks only for adjudicated claims where the total adjustment amount is equal to the total charge amount. The total charge amount must be at least $0.01.
To highlight and copy the code below to your clipboard, simply click the Copy button.
select case when c is null then 0 else c end as ttl_claims, date_axis from ( select count(claim_note) as c, mo, yr from ( select extract(month from claimdate) as mo, extract(year from claimdate) as yr, claimdate, claim_note from ( select claimdate, claim_note, invoiceno, charge, payment, adjustment from claims1 where charge=adjustment and claimdate between :start_date and :end_date and charge >= 0.01 order by claimdate ) ) a group by a.yr, a.mo ) b right outer join ( select ( yr || ' / ' || mo) as date_axis, mo, yr from ( select distinct extract(month from date1) as mo, extract(year from date1) as yr from archive_transactions where date1 between :start_date and :end_date order by yr, mo ) ) t on t.yr=b.yr and t.mo=b.mo