About
This SQL report summarizes total visits and patients by insurance for a date range you specify.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- Visits and patients are classified by the primary insurance listed in the CLAIMS1 table.
- This report presumes that one claim = one visit, and one visit = one claim.
- This does not necessarily mean a visit was face-to-face with a provider. A claim which includes only a blood draw (36415) is a “visit.”
- A claim which was split, i.e. 4 line items sent on one claim then a second claim sent with a “forgotten” line item, will count as two “visits”
- Two dates of service billed on the same claim will count as one “visit”
- Only the primary billed insurance counts for purposes of this report.
- The number of patients seen is number of distinct patients who had a claim with that insurance. That means that a particular patient may appear twice on the report if he had 2 different insurances during the period.
- For example, a patient who was seen once with both ABC and XYZ insurance during the period will appear in both the ABC - Patients column and the XYZ - Patients column.
- Thus, if you run a total of unique patients seen during the period, that number will be smaller than the total number of unique patient-insurance pairs.
Code
To highlight and copy the code below to your clipboard, simply click the Copy button.
select myvisits.*, patients, patient_mix from ( select ins_carrier_name, visits, ( round((100*visits/myvisittotals),2) || ' %') as visit_mix from ( select ins_Carrier_name, count(patno) as visits, (select count(patno) as totalvisits from (select patno, claimdate from claims1 where claimdate between :start_date and :end_date ) t5 ) as myvisittotals from ( select patno, claimdate, ins_carrier_code_1, ins_Carrier_name from claims1 left outer join ins_Carrier on ins_carrier.ins_carrier_code= claims1.ins_carrier_code_1 where claimdate between :start_date and :end_date and charge >= 0.01) t2 group by ins_carrier_name )t4 ) myvisits left outer join ( select ins_carrier_name, patients, ( round((100*patients/mypatienttotals),2) || ' %') as patient_mix from ( select ins_Carrier_name, count(patno) as patients, (select count(patno) as totalpatients from (select distinct patno, ins_carrier_code_1 from claims1 where claimdate between :start_date and :end_date ) t6) as mypatienttotals from ( select distinct patno, ins_carrier_code_1, ins_Carrier_name from claims1 left outer join ins_Carrier on ins_carrier.ins_carrier_code= claims1.ins_carrier_code_1 where claimdate between :start_date and :end_date and charge >= 0.01 ) t1 group by ins_carrier_name ) t3 ) mypatients on mypatients.ins_Carrier_name = myvisits.ins_carrier_name