About

Shows all active patients, their PCPs, and who's done the most checkups on them beginning from a reference date you specify.

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

Caveats

• Checkup attribution is defined as “well note with a certain rendering provider”.
• PCP is who they’re assigned to in the Patient Register.
• When a patient has had several checkups amongst two or more providers, they will be shown only with the doc who has done the most.
• In case or a two-way tie, the patient will show twice with an entry for both providers listed as Most Doc.
• In case of a three-way tie, the patient will show three times with an entry for each provider listed as Most Doc.

SQL Code: Firebird

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

Copy

select  patno, s1.staffname as pcp, highest_count, s2.staffname as most_doc from
(


select patno, max(n) as highest_count from


(select patno, p_addr_id, count(p_addr_id) as n from physical where date1 >= :start_date  group by patno, p_addr_id) 

 group by patno


) w


left outer join
(select patno, p_addr_id as doc, count(p_addr_id) as n from physical where date1 >= :start_date  group by patno, p_addr_id) 
 w2 on w.patno = w2.patno and w.highest_count=w2.n

inner join register on register.patno = w.patno
left outer join staff1 s1 on s1.staffid = register.addr_id
left outer join staff1 s2 on s2.staffid = w2.doc
where register.patno >99 and status_pat = 'ACTIVE' and register.lname <> 'TESTPATIENT'

SQL Code: MySQL

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

Copy

select  w.patno, s1.staffname as pcp, highest_count, s2.staffname as most_doc from
(
select patno, max(n) as highest_count from
(select patno, p_addr_id, count(p_addr_id) as n from physical where date1 >= :start_date  group by patno, p_addr_id)  t1
 group by patno
) w
left outer join
(select patno, p_addr_id as doc, count(p_addr_id) as n from physical where date1 >= :start_date  group by patno, p_addr_id) 
 w2 on w.patno = w2.patno and w.highest_count=w2.n

inner join register on register.patno = w.patno
left outer join staff1 s1 on s1.staffid = register.addr_id
left outer join staff1 s2 on s2.staffid = w2.doc
where register.patno >99 and status_pat = 'ACTIVE' and register.lname <> 'TESTPATIENT'