| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This report looks at all the completed appointments on the schedule, finds the associated billed insurance, and counts the total number of encounters.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- Only appointments completed on the Schedule count (i.e. inpatients/newborns) will not appear because practices typically do not put them on the F7/Schedule.
- If a patient is listed on the schedule twice (e.g. once in the nurse column and again in the provider column), the count will appear under both the Nurse header and the provider’s header.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select staffname, ins_carrier_name, (yr || ' / ' || mo) as yr_mo, count(patno) as visit_by_insco from
(
select sched.*, ins_carrier_name
from (
select patno, appt_date, extract(year from appt_date)as yr, extract(month from appt_date) as mo, staffname from schedule
inner join staff1 on staff1.staffid= schedule.addr_id
where appt_date between :start_date and :end_date
and patno >0 and schedule.visit_status = 'Completed'
order by appt_date
) sched
left outer join ( select distinct archive_transactions.patno, archive_transactions.date1, ins_carrier_code, ins_carrier_name from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
where archive_transactions.date1 between :start_date and :end_date and archive_transactions.cptcode not in ('1','2','3','4') and archive_transactions.archive_flag =1)
at1 on (at1.patno=sched.patno) and (at1.date1 = sched.appt_date)
order by staffname, yr, mo, ins_carrier_code
) a group by yr, mo, staffname, ins_carrier_nameSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select staffname, ins_carrier_name, (yr || ' / ' || mo) as yr_mo, count(patno) as visit_by_insco from
(
select sched.*, ins_carrier_name
from (
select patno, appt_date, extract(year from appt_date)as yr, extract(month from appt_date) as mo, staffname from schedule
inner join staff1 on staff1.staffid= schedule.addr_id
where appt_date between :start_date and :end_date
and patno >0 and schedule.visit_status = 'Completed'
order by appt_date
) sched
left outer join ( select distinct archive_transactions.patno, archive_transactions.date1, ins_carrier.ins_carrier_code, ins_carrier_name from archive_transactions
inner join ins_carrier on ins_carrier.ins_carrier_code = archive_transactions.ins_carrier_code
where archive_transactions.date1 between :start_date and :end_date and archive_transactions.cptcode not in ('1','2','3','4') and archive_transactions.archive_flag =1)
at1 on (at1.patno=sched.patno) and (at1.date1 = sched.appt_date)
order by staffname, yr, mo, ins_carrier_code
) a group by yr, mo, staffname, ins_carrier_name
