About

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.

Copy

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