About

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

Caveats

• Only patients with status ACTIVE are shown here.  We are assuming patients who are already set to INACTIVE, DISCHARGED, TRANSFERRED are of no interest to you.
• “Not seen since” means no sick visit or well visit note.  If you saw them e.g. for a flu shot and billed a flu shot only, or you saw them in the hospital and filed a claim (but did not put an encounter note in OP), that doesn’t count as “seen” for purposes of this report.

Code

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

Copy

select * from (
select register.patno, fname, lname, birthdat, register.status_pat, register.status_date,  r_sick as last_sick, r_well as last_well, 
case when r_sick is null and r_well is null then null when r_well is null then r_sick when r_sick is null then r_well when r_sick > r_well then r_sick else r_well end as last_seen from register
left outer join  (select patno, max(date1) as r_sick from enc_note group by patno) last_sick on last_sick.patno = register.patno
left outer join  (select patno, max(date1) as r_well from physical group by patno) last_well on last_well.patno = register.patno

where register.status_pat = 'ACTIVE'
and register.patno > 99
order by patno
) t1  where last_seen is null or last_seen <= :ref_date