SQL: Rendering Provider vs PCP

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

For a date range you specify, shows all patients seen for sick and well visits along with the rendering provider for that visit, along with the patient’s assigned PCP in the REGISTER.

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.

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.

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.

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