About

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

• “Seen for sick and well visits” means there is an encounter note or well visit note in their chart (as opposed to who they were scheduled with or who the billing provider was).
• This shows who the patient's PCP is now -- not necessarily who the PCP was at the time of the visit.

Report Usage Ideas

If Dr. X is only able to see 50% of his/her total patient visits in the clinic, then Dr. X might need to slow growth or shunt new patients to other providers for a bit. Alternatively, if Dr. X is seeing 70% of patients that aren’t his/hers, then X probably would benefit from having new patients shunted their way.

SQL Code: Firebird

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

Copy

select patno, fname, lname, s2.staffname as pcp, appts.date1 as appt_date, s1.staffname as rendering_provider, appt_type from
(
select patno, date1, p_addr_id, 'sick' as appt_type from enc_note where date1 between :start_date and :end_Date
union
select patno, date1, p_addr_id, 'well' as appt_type from physical where date1 between :start_date and :end_Date
) appts 
inner join register r on r.patno = appts.patno
inner join staff1 s1 on s1.staffid = appts.p_addr_id
inner join staff1 s2 on s2.staffid = r.addr_id

SQL Code: MySQL

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

Copy

select appts.patno, fname, lname, s2.staffname as pcp, appts.date1 as appt_date, s1.staffname as rendering_provider, appt_type from
(
select patno, date1, p_addr_id, 'sick' as appt_type from enc_note where date1 between :start_date and :end_date
union
select patno, date1, p_addr_id, 'well' as appt_type from physical where date1 between :start_date and :end_date
) appts 
inner join register r on r.patno = appts.patno
inner join staff1 s1 on s1.staffid = appts.p_addr_id
inner join staff1 s2 on s2.staffid = r.addr_id

Variation SQL Code: Firebird and MySQL

This variation summarizes the data in the above version. It shows the rendering provider for each visit and the number of visits, classified as “Own patient” (rendering provider was the PCP) or “not PCP” (rendering provider was not the PCP.)

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

Copy

select rendering_provider, PCP_status, count(appt_date) as n from(
select s2.staffname as pcp, appts.date1 as appt_date, s1.staffname as rendering_provider, case when s2.staffname = s1.staffname then 'Own Patient' else 'not PCP' end as PCP_status from
(
select patno, date1, p_addr_id from enc_note where date1 between :start_date and :end_Date
union
select patno, date1, p_addr_id from physical where date1 between :start_date and :end_Date
) appts 
inner join register r on r.patno = appts.patno
inner join staff1 s1 on s1.staffid = appts.p_addr_id
inner join staff1 s2 on s2.staffid = r.addr_id
) a
group by 1,2